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_4 dependencies on CONTAINS

Line 70: p_contains_str VARCHAR2) RETURN NUMBER IS

66: END IF;
67: END ;
68: FUNCTION check_estimate_hits (
69: p_entity VARCHAR2,
70: p_contains_str VARCHAR2) RETURN NUMBER IS
71:
72: ustatus VARCHAR2(255);
73: dstatus VARCHAR2(255);
74: l_bool BOOLEAN;

Line 83: g_index_owner||'.'||'HZ_STAGE_PARTIES_T1',p_contains_str, false);

79: END IF;
80: IF p_entity='PARTY' THEN
81:
82: l_hits := CTX_QUERY.count_hits(
83: g_index_owner||'.'||'HZ_STAGE_PARTIES_T1',p_contains_str, false);
84: ELSIF p_entity='PARTY_SITES' THEN
85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN

Line 86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);

82: l_hits := CTX_QUERY.count_hits(
83: g_index_owner||'.'||'HZ_STAGE_PARTIES_T1',p_contains_str, false);
84: ELSIF p_entity='PARTY_SITES' THEN
85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN
88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN

Line 89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);

85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN
88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN
91: l_hits := CTX_QUERY.count_hits(
92: g_index_owner||'.'||'HZ_STAGE_CPT_T1',p_contains_str, false);
93: END IF;

Line 92: g_index_owner||'.'||'HZ_STAGE_CPT_T1',p_contains_str, false);

88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN
91: l_hits := CTX_QUERY.count_hits(
92: g_index_owner||'.'||'HZ_STAGE_CPT_T1',p_contains_str, false);
93: END IF;
94: RETURN floor(l_hits/2) ;
95:
96:

Line 882: l_party_contains_str VARCHAR2(32000);

878: p_party_max_score NUMBER,
879: p_denorm_max_score NUMBER,
880: p_non_denorm_max_score NUMBER,
881: p_threshold NUMBER) RETURN VARCHAR2 IS
882: l_party_contains_str VARCHAR2(32000);
883: l_party_filter VARCHAR2(1) := null;
884: l_prim_temp VARCHAR2(4000);
885: l_denorm_str VARCHAR2(4000);
886: BEGIN

Line 926: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

922:
923: IF l_denorm_str IS NOT NULL THEN
924: IF (p_non_denorm_max_score+50)>=p_threshold THEN
925: l_denorm_str := NULL;
926: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
927: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
928: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
929: l_denorm_str := NULL;
930: END IF;

Line 928: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);

924: IF (p_non_denorm_max_score+50)>=p_threshold THEN
925: l_denorm_str := NULL;
926: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
927: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
928: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
929: l_denorm_str := NULL;
930: END IF;
931: ELSE
932: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

Line 932: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

928: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
929: l_denorm_str := NULL;
930: END IF;
931: ELSE
932: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
933: END IF;
934: IF lengthb(l_party_contains_str) > 4000 THEN
935: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
936: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 934: IF lengthb(l_party_contains_str) > 4000 THEN

930: END IF;
931: ELSE
932: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
933: END IF;
934: IF lengthb(l_party_contains_str) > 4000 THEN
935: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
936: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
937: FND_MSG_PUB.ADD;
938: RAISE FND_API.G_EXC_ERROR;

Line 940: IF (p_match_str = ' AND ' OR p_party_max_score

936: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
937: FND_MSG_PUB.ADD;
938: RAISE FND_API.G_EXC_ERROR;
939: END IF;
940: IF (p_match_str = ' AND ' OR p_party_max_score 941: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
942: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
943: END IF;
944: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 942: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

938: RAISE FND_API.G_EXC_ERROR;
939: END IF;
940: IF (p_match_str = ' AND ' OR p_party_max_score 941: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
942: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
943: END IF;
944: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
945: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
946: END IF;

Line 945: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

941: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
942: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
943: END IF;
944: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
945: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
946: END IF;
947: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
948: hz_utility_v2pub.debug(p_message=>'p_denorm_str = '||p_denorm_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
949: END IF;

Line 950: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';

946: END IF;
947: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
948: hz_utility_v2pub.debug(p_message=>'p_denorm_str = '||p_denorm_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
949: END IF;
950: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
951: ELSE
952: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
953: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
954: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 953: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

949: END IF;
950: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
951: ELSE
952: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
953: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
954: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
955: END IF;
956: RETURN l_party_contains_str;
957: END IF;

Line 954: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

950: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
951: ELSE
952: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
953: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
954: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
955: END IF;
956: RETURN l_party_contains_str;
957: END IF;
958: END;

Line 956: RETURN l_party_contains_str;

952: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
953: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
954: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
955: END IF;
956: RETURN l_party_contains_str;
957: END IF;
958: END;
959: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
960: l_contains_str VARCHAR2(32000);

Line 960: l_contains_str VARCHAR2(32000);

956: RETURN l_party_contains_str;
957: END IF;
958: END;
959: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
960: l_contains_str VARCHAR2(32000);
961: l_contains_str_temp VARCHAR2(32000);
962: l_den_contains_str VARCHAR2(32000);
963: l_den_contains_str_temp VARCHAR2(32000);
964: l_filter_str VARCHAR2(4000) := null;

Line 961: l_contains_str_temp VARCHAR2(32000);

957: END IF;
958: END;
959: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
960: l_contains_str VARCHAR2(32000);
961: l_contains_str_temp VARCHAR2(32000);
962: l_den_contains_str VARCHAR2(32000);
963: l_den_contains_str_temp VARCHAR2(32000);
964: l_filter_str VARCHAR2(4000) := null;
965: l_prim_temp VARCHAR2(4000) := null;

Line 962: l_den_contains_str VARCHAR2(32000);

958: END;
959: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
960: l_contains_str VARCHAR2(32000);
961: l_contains_str_temp VARCHAR2(32000);
962: l_den_contains_str VARCHAR2(32000);
963: l_den_contains_str_temp VARCHAR2(32000);
964: l_filter_str VARCHAR2(4000) := null;
965: l_prim_temp VARCHAR2(4000) := null;
966: l_prim_temp_den VARCHAR2(4000) := null;

Line 963: l_den_contains_str_temp VARCHAR2(32000);

959: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
960: l_contains_str VARCHAR2(32000);
961: l_contains_str_temp VARCHAR2(32000);
962: l_den_contains_str VARCHAR2(32000);
963: l_den_contains_str_temp VARCHAR2(32000);
964: l_filter_str VARCHAR2(4000) := null;
965: l_prim_temp VARCHAR2(4000) := null;
966: l_prim_temp_den VARCHAR2(4000) := null;
967: BEGIN

Line 973: -- Dynamic setup of party site contains str

969: hz_utility_v2pub.debug(p_message=>'INIT_PARTY_SITES_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
970: END IF;
971: x_denorm_str := NULL;
972:
973: -- Dynamic setup of party site contains str
974: --
975: -- For each primary transformation add to intermedia query if it
976: -- is not null
977: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 978: l_contains_str_temp := null;

974: --
975: -- For each primary transformation add to intermedia query if it
976: -- is not null
977: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
978: l_contains_str_temp := null;
979: l_den_contains_str_temp := null;
980: l_filter_str := null;
981:
982: -- Setup query string for POSTAL_CODE

Line 979: l_den_contains_str_temp := null;

975: -- For each primary transformation add to intermedia query if it
976: -- is not null
977: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
978: l_contains_str_temp := null;
979: l_den_contains_str_temp := null;
980: l_filter_str := null;
981:
982: -- Setup query string for POSTAL_CODE
983: l_prim_temp := null;

Line 989: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);

985: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
986: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
987: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
988: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
989: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
990: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
991:
992: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
993: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

Line 990: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);

986: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
987: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
988: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
989: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
990: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
991:
992: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
993: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
994: END LOOP;

Line 992: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);

988: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
989: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
990: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
991:
992: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
993: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
994: END LOOP;
995: -- Add the search criteria to query string
996: IF lengthb(l_contains_str) > 4000 THEN

Line 993: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

989: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
990: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
991:
992: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
993: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
994: END LOOP;
995: -- Add the search criteria to query string
996: IF lengthb(l_contains_str) > 4000 THEN
997: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 996: IF lengthb(l_contains_str) > 4000 THEN

992: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
993: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
994: END LOOP;
995: -- Add the search criteria to query string
996: IF lengthb(l_contains_str) > 4000 THEN
997: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
998: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
999: FND_MSG_PUB.ADD;
1000: RAISE FND_API.G_EXC_ERROR;

Line 1002: x_denorm_str := l_den_contains_str;

998: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
999: FND_MSG_PUB.ADD;
1000: RAISE FND_API.G_EXC_ERROR;
1001: END IF;
1002: x_denorm_str := l_den_contains_str;
1003: RETURN l_contains_str;
1004: END;
1005:
1006: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1003: RETURN l_contains_str;

999: FND_MSG_PUB.ADD;
1000: RAISE FND_API.G_EXC_ERROR;
1001: END IF;
1002: x_denorm_str := l_den_contains_str;
1003: RETURN l_contains_str;
1004: END;
1005:
1006: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1007: l_contains_str VARCHAR2(32000);

Line 1007: l_contains_str VARCHAR2(32000);

1003: RETURN l_contains_str;
1004: END;
1005:
1006: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1007: l_contains_str VARCHAR2(32000);
1008: l_contains_str_temp VARCHAR2(32000);
1009: l_den_contains_str VARCHAR2(32000);
1010: l_den_contains_str_temp VARCHAR2(32000);
1011: l_filter_str VARCHAR2(4000) := null;

Line 1008: l_contains_str_temp VARCHAR2(32000);

1004: END;
1005:
1006: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1007: l_contains_str VARCHAR2(32000);
1008: l_contains_str_temp VARCHAR2(32000);
1009: l_den_contains_str VARCHAR2(32000);
1010: l_den_contains_str_temp VARCHAR2(32000);
1011: l_filter_str VARCHAR2(4000) := null;
1012: l_prim_temp VARCHAR2(4000) := null;

Line 1009: l_den_contains_str VARCHAR2(32000);

1005:
1006: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1007: l_contains_str VARCHAR2(32000);
1008: l_contains_str_temp VARCHAR2(32000);
1009: l_den_contains_str VARCHAR2(32000);
1010: l_den_contains_str_temp VARCHAR2(32000);
1011: l_filter_str VARCHAR2(4000) := null;
1012: l_prim_temp VARCHAR2(4000) := null;
1013: l_prim_temp_den VARCHAR2(4000) := null;

Line 1010: l_den_contains_str_temp VARCHAR2(32000);

1006: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1007: l_contains_str VARCHAR2(32000);
1008: l_contains_str_temp VARCHAR2(32000);
1009: l_den_contains_str VARCHAR2(32000);
1010: l_den_contains_str_temp VARCHAR2(32000);
1011: l_filter_str VARCHAR2(4000) := null;
1012: l_prim_temp VARCHAR2(4000) := null;
1013: l_prim_temp_den VARCHAR2(4000) := null;
1014: BEGIN

Line 1023: l_contains_str VARCHAR2(32000);

1019: RETURN NULL;
1020: END;
1021:
1022: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1023: l_contains_str VARCHAR2(32000);
1024: l_contains_str_temp VARCHAR2(32000);
1025: l_den_contains_str VARCHAR2(32000);
1026: l_den_contains_str_temp VARCHAR2(32000);
1027: l_filter_str VARCHAR2(4000) := null;

Line 1024: l_contains_str_temp VARCHAR2(32000);

1020: END;
1021:
1022: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1023: l_contains_str VARCHAR2(32000);
1024: l_contains_str_temp VARCHAR2(32000);
1025: l_den_contains_str VARCHAR2(32000);
1026: l_den_contains_str_temp VARCHAR2(32000);
1027: l_filter_str VARCHAR2(4000) := null;
1028: l_prim_temp VARCHAR2(4000) := null;

Line 1025: l_den_contains_str VARCHAR2(32000);

1021:
1022: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1023: l_contains_str VARCHAR2(32000);
1024: l_contains_str_temp VARCHAR2(32000);
1025: l_den_contains_str VARCHAR2(32000);
1026: l_den_contains_str_temp VARCHAR2(32000);
1027: l_filter_str VARCHAR2(4000) := null;
1028: l_prim_temp VARCHAR2(4000) := null;
1029: l_prim_temp_den VARCHAR2(4000) := null;

Line 1026: l_den_contains_str_temp VARCHAR2(32000);

1022: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1023: l_contains_str VARCHAR2(32000);
1024: l_contains_str_temp VARCHAR2(32000);
1025: l_den_contains_str VARCHAR2(32000);
1026: l_den_contains_str_temp VARCHAR2(32000);
1027: l_filter_str VARCHAR2(4000) := null;
1028: l_prim_temp VARCHAR2(4000) := null;
1029: l_prim_temp_den VARCHAR2(4000) := null;
1030: BEGIN

Line 1043: p_party_contains_str VARCHAR2,

1039: p_dup_party_id NUMBER,
1040: p_restrict_sql VARCHAR2,
1041: p_match_str VARCHAR2,
1042: p_search_merged VARCHAR2,
1043: p_party_contains_str VARCHAR2,
1044: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1045: l_sqlstr VARCHAR2(4000);
1046: l_search_merged VARCHAR2(1);
1047: BEGIN

Line 1060: IF p_restrict_sql IS NULL AND NOT g_other_party_level_attribs AND NOT (p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1) THEN

1056: END IF;
1057: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1058: hz_utility_v2pub.debug(p_message=>'Search Merged Flag - '||l_search_merged,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1059: END IF;
1060: IF p_restrict_sql IS NULL AND NOT g_other_party_level_attribs AND NOT (p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1) THEN
1061: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1062: 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);
1063: END IF;
1064: OPEN x_cursor FOR

Line 1091: IF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN

1087: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1088: l_sqlstr := l_sqlstr || ' (TX36 like :TX36||'' %'' ';
1089: l_sqlstr := l_sqlstr || ' )) ';
1090: END IF;
1091: IF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1092: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1093: hz_utility_v2pub.debug(p_message=>'p_party_contains_str string is NOT NULL and other conditions met',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1094: END IF;
1095: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

Line 1093: hz_utility_v2pub.debug(p_message=>'p_party_contains_str string is NOT NULL and other conditions met',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1089: l_sqlstr := l_sqlstr || ' )) ';
1090: END IF;
1091: IF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1092: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1093: hz_utility_v2pub.debug(p_message=>'p_party_contains_str string is NOT NULL and other conditions met',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1094: END IF;
1095: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1096: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX11||'' %'' ';
1097: l_sqlstr := l_sqlstr || ' )) ';

Line 1114: IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN

1110: END IF;
1111: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1112: hz_utility_v2pub.debug(p_message=>'l_sqlstr is - '||l_sqlstr,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1113: END IF;
1114: IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1115: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1116: hz_utility_v2pub.debug(p_message=>'IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1117: END IF;
1118: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id

Line 1116: hz_utility_v2pub.debug(p_message=>'IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1112: hz_utility_v2pub.debug(p_message=>'l_sqlstr is - '||l_sqlstr,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1113: END IF;
1114: IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1115: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1116: hz_utility_v2pub.debug(p_message=>'IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1117: END IF;
1118: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1119: ,g_party_stage_rec.TX36
1120: ,g_party_stage_rec.TX36

Line 1132: ELSIF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN

1128: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1129: ,g_party_stage_rec.TX36
1130: ,g_party_stage_rec.TX36
1131: ;
1132: ELSIF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1133: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1134: hz_utility_v2pub.debug(p_message=>'ELSIF p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1135: END IF;
1136: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id

Line 1134: hz_utility_v2pub.debug(p_message=>'ELSIF p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1130: ,g_party_stage_rec.TX36
1131: ;
1132: ELSIF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1133: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1134: hz_utility_v2pub.debug(p_message=>'ELSIF p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1135: END IF;
1136: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1137: ,g_party_site_stage_list(1).TX11
1138: ,g_party_site_stage_list(1).TX11

Line 1151: p_contains_str VARCHAR2,

1147: END;
1148: PROCEDURE open_party_cursor(
1149: p_dup_party_id NUMBER,
1150: p_restrict_sql VARCHAR2,
1151: p_contains_str VARCHAR2,
1152: p_search_ctx_id NUMBER,
1153: p_match_str VARCHAR2,
1154: p_search_merged VARCHAR2,
1155: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1170: IF p_contains_str IS NULL THEN

1166: l_search_merged := 'N';
1167: ELSE
1168: l_search_merged := p_search_merged;
1169: END IF;
1170: IF p_contains_str IS NULL THEN
1171: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1172: 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);
1173: END IF;
1174: OPEN x_cursor FOR

Line 1172: 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);

1168: l_search_merged := p_search_merged;
1169: END IF;
1170: IF p_contains_str IS NULL THEN
1171: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1172: 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);
1173: END IF;
1174: OPEN x_cursor FOR
1175: SELECT PARTY_ID , TX2, TX4, TX8, lpad(rtrim(TX41),9,chr(48)), TX44
1176: FROM HZ_STAGED_PARTIES stage

Line 1189: WHERE contains( concat_col, p_contains_str)>0

1185: END IF;
1186: OPEN x_cursor FOR
1187: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX2, TX4, TX8, lpad(rtrim(TX41),9,chr(48)), TX44
1188: FROM HZ_STAGED_PARTIES stage
1189: WHERE contains( concat_col, p_contains_str)>0
1190: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1191: AND( (l_search_merged ='Y' )
1192: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1193: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

Line 1202: WHERE contains( concat_col, p_contains_str)>0

1198: END IF;
1199: OPEN x_cursor FOR
1200: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX2, TX4, TX8, lpad(rtrim(TX41),9,chr(48)), TX44
1201: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1202: WHERE contains( concat_col, p_contains_str)>0
1203: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1204: AND d.party_id = stage.party_id
1205: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1206: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

Line 1227: ' WHERE contains( concat_col, :cont)>0'||

1223: END IF;
1224: IF p_search_ctx_id IS NULL THEN
1225: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX2, TX4, TX8, lpad(rtrim(TX41),9,chr(48)), TX44'||
1226: ' FROM HZ_STAGED_PARTIES stage'||
1227: ' WHERE contains( concat_col, :cont)>0'||
1228: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1229: ' AND ('||p_restrict_sql||')' ||
1230: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1231: IF l_search_merged = 'Y' THEN

Line 1239: output_long_strings('Party Contains String = '||p_contains_str);

1235: ELSE
1236: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1237: END IF;
1238: output_long_strings('----------------------------------------------------------');
1239: output_long_strings('Party Contains String = '||p_contains_str);
1240: output_long_strings('Restrict Sql = '||p_restrict_sql);
1241: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1242: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1243: ,p_dup_party_id, p_dup_party_id;

Line 1241: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1237: END IF;
1238: output_long_strings('----------------------------------------------------------');
1239: output_long_strings('Party Contains String = '||p_contains_str);
1240: output_long_strings('Restrict Sql = '||p_restrict_sql);
1241: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1242: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1243: ,p_dup_party_id, p_dup_party_id;
1244: END IF;
1245: END IF;

Line 1272: p_contains_str VARCHAR2,

1268: PROCEDURE open_party_site_cursor(
1269: p_dup_party_id NUMBER,
1270: p_party_id NUMBER,
1271: p_restrict_sql VARCHAR2,
1272: p_contains_str VARCHAR2,
1273: p_search_ctx_id NUMBER,
1274: p_search_merged VARCHAR2,
1275: p_search_rel_sites VARCHAR2,
1276: p_person_api VARCHAR2,

Line 1304: WHERE contains( concat_col, p_contains_str)>0

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

Line 1313: WHERE contains( concat_col, p_contains_str)>0

1309: ELSE
1310: OPEN x_cursor FOR
1311: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1312: FROM HZ_STAGED_PARTY_SITES stage
1313: WHERE contains( concat_col, p_contains_str)>0
1314: AND( (l_search_merged ='Y' )
1315: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1316: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1317: AND stage.party_id = p_party_id

Line 1321: WHERE contains( concat_col, p_contains_str)>0

1317: AND stage.party_id = p_party_id
1318: UNION
1319: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX4, TX3
1320: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1321: WHERE contains( concat_col, p_contains_str)>0
1322: AND( (l_search_merged ='Y' )
1323: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1324: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1325: AND r.object_id = p_party_id

Line 1343: WHERE contains( concat_col, p_contains_str)>0

1339: END IF;
1340: OPEN x_cursor FOR
1341: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1342: FROM HZ_STAGED_PARTY_SITES stage
1343: WHERE contains( concat_col, p_contains_str)>0
1344: AND EXISTS (
1345: SELECT 1 FROM HZ_STAGED_PARTIES p
1346: WHERE p.PARTY_ID = stage.PARTY_ID
1347: AND( (l_search_merged ='Y' )

Line 1363: WHERE contains( concat_col, p_contains_str)>0

1359: IF p_person_api = 'Y' THEN
1360: OPEN x_cursor FOR
1361: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1362: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1363: WHERE contains( concat_col, p_contains_str)>0
1364: AND( (l_search_merged ='Y' )
1365: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1366: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1367: AND d.search_context_id = p_search_ctx_id

Line 1373: WHERE contains( concat_col, p_contains_str)>0

1369: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1370: UNION
1371: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX4, TX3
1372: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1373: WHERE contains( concat_col, p_contains_str)>0
1374: AND( (l_search_merged ='Y' )
1375: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1376: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1377: AND d.search_context_id = p_search_ctx_id

Line 1386: WHERE contains( concat_col, p_contains_str)>0

1382: ELSE
1383: OPEN x_cursor FOR
1384: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1385: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1386: WHERE contains( concat_col, p_contains_str)>0
1387: AND( (l_search_merged ='Y' )
1388: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1389: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1390: AND d.search_context_id = p_search_ctx_id

Line 1431: ' WHERE contains( concat_col, :cont)>0'||

1427: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1428: END IF;
1429: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1430: ' FROM HZ_STAGED_PARTY_SITES stage'||
1431: ' WHERE contains( concat_col, :cont)>0'||
1432: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1433: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1434: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1435: ' AND (ORG_CONTACT_ID IS NULL ' ||

Line 1441: ' WHERE contains( concat_col, :cont)>0'||

1437: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1438: ' UNION ' ||
1439: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1440: ' FROM HZ_STAGED_PARTY_SITES stage'||
1441: ' WHERE contains( concat_col, :cont)>0'||
1442: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1443: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1444: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1445: ' AND ORG_CONTACT_ID IN ' ||

Line 1451: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1447: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1448: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1449: ' and ('||p_restrict_sql1|| ') )' ||
1450: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1451: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1452: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1453: ELSE
1454: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1455: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1452: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1448: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1449: ' and ('||p_restrict_sql1|| ') )' ||
1450: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1451: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1452: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1453: ELSE
1454: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1455: ' FROM HZ_STAGED_PARTY_SITES stage'||
1456: ' WHERE contains( concat_col, :cont)>0'||

Line 1456: ' WHERE contains( concat_col, :cont)>0'||

1452: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1453: ELSE
1454: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1455: ' FROM HZ_STAGED_PARTY_SITES stage'||
1456: ' WHERE contains( concat_col, :cont)>0'||
1457: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1458: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1459: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1460: ' AND EXISTS ('||

Line 1466: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1462: ' WHERE p.party_id = stage.party_id ' ||
1463: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1464: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1465: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1466: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1467: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1468: ,p_dup_party_id, p_dup_party_id;
1469: END IF;
1470: ELSE

Line 1473: ' WHERE contains( concat_col, :cont)>0'||

1469: END IF;
1470: ELSE
1471: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1472: ' FROM HZ_STAGED_PARTY_SITES stage'||
1473: ' WHERE contains( concat_col, :cont)>0'||
1474: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1475: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1476: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1477: ' AND EXISTS ('||

Line 1483: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1479: ' WHERE p.party_id = stage.party_id ' ||
1480: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1481: ' AND ('||p_restrict_sql||')' ||
1482: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1483: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1484: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1485: ,p_dup_party_id, p_dup_party_id;
1486: END IF;
1487: END IF;

Line 1489: output_long_strings('Party Site Contains String = '||p_contains_str);

1485: ,p_dup_party_id, p_dup_party_id;
1486: END IF;
1487: END IF;
1488: output_long_strings('----------------------------------------------------------');
1489: output_long_strings('Party Site Contains String = '||p_contains_str);
1490: output_long_strings('Restrict Sql = '||p_restrict_sql);
1491: exception
1492: when others then
1493: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1517: p_contains_str VARCHAR2,

1513: PROCEDURE open_contact_cursor(
1514: p_dup_party_id NUMBER,
1515: p_party_id NUMBER,
1516: p_restrict_sql VARCHAR2,
1517: p_contains_str VARCHAR2,
1518: p_search_ctx_id NUMBER,
1519: p_search_merged VARCHAR2,
1520: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1521: l_sqlstr VARCHAR2(4000);

Line 1545: WHERE contains( concat_col, p_contains_str)>0

1541: END IF;
1542: OPEN x_cursor FOR
1543: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1544: FROM HZ_STAGED_CONTACTS stage
1545: WHERE contains( concat_col, p_contains_str)>0
1546: AND EXISTS (
1547: SELECT 1 FROM HZ_STAGED_PARTIES p
1548: WHERE p.PARTY_ID = stage.PARTY_ID
1549: AND( (l_search_merged ='Y' )

Line 1568: WHERE contains( concat_col, p_contains_str)>0

1564: END IF;
1565: OPEN x_cursor FOR
1566: SELECT ORG_CONTACT_ID, PARTY_ID
1567: FROM HZ_STAGED_CONTACTS stage
1568: WHERE contains( concat_col, p_contains_str)>0
1569: AND EXISTS (
1570: SELECT 1 FROM HZ_STAGED_PARTIES p
1571: WHERE p.PARTY_ID = stage.PARTY_ID
1572: AND( (l_search_merged ='Y' )

Line 1587: WHERE contains( concat_col, p_contains_str)>0

1583: END IF;
1584: OPEN x_cursor FOR
1585: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1586: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1587: WHERE contains( concat_col, p_contains_str)>0
1588: AND d.search_context_id = p_search_ctx_id
1589: AND d.party_id = stage.party_id
1590: AND( (l_search_merged ='Y' )
1591: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1621: ' WHERE contains( concat_col, :cont)>0'||

1617: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1618: END IF;
1619: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1620: ' FROM HZ_STAGED_CONTACTS stage'||
1621: ' WHERE contains( concat_col, :cont)>0'||
1622: ' AND EXISTS ('||
1623: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1624: ' WHERE p.party_id = stage.party_id ' ||
1625: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1631: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1627: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1628: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1629: ' AND ('||p_restrict_sql||')' ||
1630: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1631: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1632: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1633: ,p_dup_party_id, p_dup_party_id;
1634: END IF;
1635: output_long_strings('----------------------------------------------------------');

Line 1636: output_long_strings('Contacts Contains String = '||p_contains_str);

1632: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1633: ,p_dup_party_id, p_dup_party_id;
1634: END IF;
1635: output_long_strings('----------------------------------------------------------');
1636: output_long_strings('Contacts Contains String = '||p_contains_str);
1637: output_long_strings('Restrict Sql = '||p_restrict_sql);
1638: exception
1639: when others then
1640: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1664: p_contains_str VARCHAR2,

1660: PROCEDURE open_contact_pt_cursor(
1661: p_dup_party_id NUMBER,
1662: p_party_id NUMBER,
1663: p_restrict_sql VARCHAR2,
1664: p_contains_str VARCHAR2,
1665: p_search_ctx_id NUMBER,
1666: p_search_merged VARCHAR2,
1667: p_search_rel_cpts VARCHAR2,
1668: p_person_api VARCHAR2,

Line 1694: WHERE contains( concat_col, p_contains_str)>0

1690: THEN
1691: OPEN x_cursor FOR
1692: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1693: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1694: WHERE contains( concat_col, p_contains_str)>0
1695: AND d.search_context_id = p_search_ctx_id
1696: AND( (l_search_merged ='Y' )
1697: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1698: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1706: WHERE contains( concat_col, p_contains_str)>0

1702: THEN
1703: OPEN x_cursor FOR
1704: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1705: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1706: WHERE contains( concat_col, p_contains_str)>0
1707: AND d.search_context_id = p_search_ctx_id
1708: AND( (l_search_merged ='Y' )
1709: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1710: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1723: WHERE contains( concat_col, p_contains_str)>0

1719: IF p_search_rel_cpts = 'N' THEN
1720: OPEN x_cursor FOR
1721: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1722: FROM HZ_STAGED_CONTACT_POINTS stage
1723: WHERE contains( concat_col, p_contains_str)>0
1724: AND( (l_search_merged ='Y' )
1725: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1726: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1727: AND stage.party_id = p_party_id;

Line 1732: WHERE contains( concat_col, p_contains_str)>0

1728: ELSE
1729: OPEN x_cursor FOR
1730: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1731: FROM HZ_STAGED_CONTACT_POINTS stage
1732: WHERE contains( concat_col, p_contains_str)>0
1733: AND( (l_search_merged ='Y' )
1734: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1735: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1736: AND stage.party_id = p_party_id

Line 1740: WHERE contains( concat_col, p_contains_str)>0

1736: AND stage.party_id = p_party_id
1737: UNION
1738: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID
1739: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1740: WHERE contains( concat_col, p_contains_str)>0
1741: AND r.object_id = p_party_id
1742: AND( (l_search_merged ='Y' )
1743: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1744: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1762: WHERE contains( concat_col, p_contains_str)>0

1758: END IF;
1759: OPEN x_cursor FOR
1760: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1761: FROM HZ_STAGED_CONTACT_POINTS stage
1762: WHERE contains( concat_col, p_contains_str)>0
1763: AND EXISTS (
1764: SELECT 1 FROM HZ_STAGED_PARTIES p
1765: WHERE p.PARTY_ID = stage.PARTY_ID
1766: AND( (l_search_merged ='Y' )

Line 1782: WHERE contains( concat_col, p_contains_str)>0

1778: IF p_person_api = 'Y' THEN
1779: OPEN x_cursor FOR
1780: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1781: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1782: WHERE contains( concat_col, p_contains_str)>0
1783: AND d.search_context_id = p_search_ctx_id
1784: AND d.party_id = stage.party_id
1785: AND( (l_search_merged ='Y' )
1786: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1792: WHERE contains( concat_col, p_contains_str)>0

1788: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1789: UNION
1790: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID
1791: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1792: WHERE contains( concat_col, p_contains_str)>0
1793: AND d.search_context_id = p_search_ctx_id
1794: AND d.party_id = r.subject_id
1795: AND r.relationship_id = oc.party_relationship_id
1796: AND oc.org_contact_id = stage.org_contact_id

Line 1805: WHERE contains( concat_col, p_contains_str)>0

1801: ELSE
1802: OPEN x_cursor FOR
1803: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1804: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1805: WHERE contains( concat_col, p_contains_str)>0
1806: AND d.search_context_id = p_search_ctx_id
1807: AND d.party_id = stage.party_id
1808: AND( (l_search_merged ='Y' )
1809: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1850: ' WHERE contains( concat_col, :cont)>0 '||

1846: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1847: END IF;
1848: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1849: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1850: ' WHERE contains( concat_col, :cont)>0 '||
1851: ' AND (stage.org_contact_id is null '||
1852: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1853: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1854: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 1860: ' WHERE contains( concat_col, :cont)>0 '||

1856: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1857: ' UNION ' ||
1858: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1859: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1860: ' WHERE contains( concat_col, :cont)>0 '||
1861: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1862: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1863: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1864: ' AND (stage.org_contact_id in ' ||

Line 1870: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1866: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1867: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1868: ' and ('||p_restrict_sql1 || ') ) )' ||
1869: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1870: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1871: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1872: ELSE
1873: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1874: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 1871: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1867: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1868: ' and ('||p_restrict_sql1 || ') ) )' ||
1869: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1870: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1871: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1872: ELSE
1873: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1874: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1875: ' WHERE contains( concat_col, :cont)>0'||

Line 1875: ' WHERE contains( concat_col, :cont)>0'||

1871: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1872: ELSE
1873: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1874: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1875: ' WHERE contains( concat_col, :cont)>0'||
1876: ' AND EXISTS ('||
1877: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1878: ' WHERE p.party_id = stage.party_id ' ||
1879: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1885: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1881: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1882: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1883: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1884: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1885: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1886: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1887: ,p_dup_party_id, p_dup_party_id;
1888: END IF;
1889: ELSE

Line 1892: ' WHERE contains( concat_col, :cont)>0'||

1888: END IF;
1889: ELSE
1890: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1891: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1892: ' WHERE contains( concat_col, :cont)>0'||
1893: ' AND EXISTS ('||
1894: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1895: ' WHERE p.party_id = stage.party_id ' ||
1896: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1902: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1898: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1899: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1900: ' AND ('||p_restrict_sql||')' ||
1901: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1902: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1903: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1904: ,p_dup_party_id, p_dup_party_id;
1905: END IF;
1906: END IF;

Line 1909: output_long_strings('Contact Points Contains String = '||p_contains_str);

1905: END IF;
1906: END IF;
1907: END IF;
1908: output_long_strings('----------------------------------------------------------');
1909: output_long_strings('Contact Points Contains String = '||p_contains_str);
1910: output_long_strings('Restrict Sql = '||p_restrict_sql);
1911: exception
1912: when others then
1913: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2030: l_party_contains_str VARCHAR2(32000);

2026: x_num_matches OUT NUMBER
2027: ) IS
2028:
2029: -- Strings to hold the generated Intermedia query strings
2030: l_party_contains_str VARCHAR2(32000);
2031: l_party_site_contains_str VARCHAR2(32000);
2032: l_contact_contains_str VARCHAR2(32000);
2033: l_contact_pt_contains_str VARCHAR2(32000);
2034: l_denorm_str VARCHAR2(32000);

Line 2031: l_party_site_contains_str VARCHAR2(32000);

2027: ) IS
2028:
2029: -- Strings to hold the generated Intermedia query strings
2030: l_party_contains_str VARCHAR2(32000);
2031: l_party_site_contains_str VARCHAR2(32000);
2032: l_contact_contains_str VARCHAR2(32000);
2033: l_contact_pt_contains_str VARCHAR2(32000);
2034: l_denorm_str VARCHAR2(32000);
2035: l_ps_denorm_str VARCHAR2(32000);

Line 2032: l_contact_contains_str VARCHAR2(32000);

2028:
2029: -- Strings to hold the generated Intermedia query strings
2030: l_party_contains_str VARCHAR2(32000);
2031: l_party_site_contains_str VARCHAR2(32000);
2032: l_contact_contains_str VARCHAR2(32000);
2033: l_contact_pt_contains_str VARCHAR2(32000);
2034: l_denorm_str VARCHAR2(32000);
2035: l_ps_denorm_str VARCHAR2(32000);
2036: l_ct_denorm_str VARCHAR2(32000);

Line 2033: l_contact_pt_contains_str VARCHAR2(32000);

2029: -- Strings to hold the generated Intermedia query strings
2030: l_party_contains_str VARCHAR2(32000);
2031: l_party_site_contains_str VARCHAR2(32000);
2032: l_contact_contains_str VARCHAR2(32000);
2033: l_contact_pt_contains_str VARCHAR2(32000);
2034: l_denorm_str VARCHAR2(32000);
2035: l_ps_denorm_str VARCHAR2(32000);
2036: l_ct_denorm_str VARCHAR2(32000);
2037: l_cpt_denorm_str VARCHAR2(32000);

Line 2124: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS

2120: call_type(2) := 'OR';
2121: END;
2122:
2123: /** Private procedure to acquire and score at party level ***/
2124: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2125: l_party_id_idx NUMBER:=1;
2126: l_ctx_id NUMBER;
2127: l_precalc_score BOOLEAN := FALSE;
2128: l_TX35_new varchar2(4000);

Line 2156: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN

2152: l_search_ctx_id,H_PARTY_ID(I));
2153: l_party_id_idx := H_PARTY_ID.COUNT+1;
2154: ELSE
2155: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2156: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2157: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2158: hz_utility_v2pub.debug(p_message=>'In eval party level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2159: END IF;
2160: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' AND p_dup_batch_id IS NULL THEN

Line 2164: IF (l_party_site_contains_str IS NULL AND

2160: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' AND p_dup_batch_id IS NULL THEN
2161: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2162: hz_utility_v2pub.debug(p_message=>'In eval party level resolution options is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2163: END IF;
2164: IF (l_party_site_contains_str IS NULL AND
2165: l_contact_contains_str IS NULL AND
2166: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2167: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2168: RAISE L_RETURN_IMM_EXC;

Line 2165: l_contact_contains_str IS NULL AND

2161: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2162: hz_utility_v2pub.debug(p_message=>'In eval party level resolution options is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2163: END IF;
2164: IF (l_party_site_contains_str IS NULL AND
2165: l_contact_contains_str IS NULL AND
2166: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2167: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2168: RAISE L_RETURN_IMM_EXC;
2169: ELSE

Line 2166: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN

2162: hz_utility_v2pub.debug(p_message=>'In eval party level resolution options is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2163: END IF;
2164: IF (l_party_site_contains_str IS NULL AND
2165: l_contact_contains_str IS NULL AND
2166: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2167: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2168: RAISE L_RETURN_IMM_EXC;
2169: ELSE
2170: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

Line 2170: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2166: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2167: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2168: RAISE L_RETURN_IMM_EXC;
2169: ELSE
2170: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2171: END IF;
2172: ELSE
2173: IF p_index>1 THEN
2174: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 2193: open_party_cursor(p_dup_party_id, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,p_search_merged,l_party_cur);

2189: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2190: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_PARTY_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2191: END IF;
2192: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2193: open_party_cursor(p_dup_party_id, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,p_search_merged,l_party_cur);
2194: END IF;
2195: LOOP
2196: FETCH l_party_cur INTO
2197: l_party_id , l_TX2, l_TX4, l_TX8, l_TX41, l_TX44;

Line 2215: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL

2211: l_party_id_idx:= l_party_id_idx+1;
2212: END IF;
2213: IF (l_party_id_idx-1)>l_max_thresh THEN
2214: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2215: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2216: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2217: AND l_contact_pt_contains_str IS NULL THEN
2218: EXIT;
2219: END IF;

Line 2217: AND l_contact_pt_contains_str IS NULL THEN

2213: IF (l_party_id_idx-1)>l_max_thresh THEN
2214: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2215: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2216: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2217: AND l_contact_pt_contains_str IS NULL THEN
2218: EXIT;
2219: END IF;
2220: CLOSE l_party_cur;
2221: IF p_index>1 THEN

Line 2246: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS

2242: ROLLBACK to eval_start;
2243: END;
2244:
2245: /** Private procedure to acquire and score at party site level ***/
2246: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2247: l_party_id_idx NUMBER:=1;
2248: l_ctx_id NUMBER;
2249: h_ps_id HZ_PARTY_SEARCH.IDList;
2250: h_ps_party_id HZ_PARTY_SEARCH.IDList;

Line 2258: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0

2254: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2255: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2256: hz_utility_v2pub.debug(p_message=>'calling the procedure eval_party_site_level',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2257: END IF;
2258: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2259: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2260: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2261: l_party_id := H_SCORES.FIRST;
2262: WHILE l_party_id IS NOT NULL LOOP

Line 2259: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and

2255: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2256: hz_utility_v2pub.debug(p_message=>'calling the procedure eval_party_site_level',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2257: END IF;
2258: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2259: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2260: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2261: l_party_id := H_SCORES.FIRST;
2262: WHILE l_party_id IS NOT NULL LOOP
2263: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

Line 2260: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN

2256: hz_utility_v2pub.debug(p_message=>'calling the procedure eval_party_site_level',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2257: END IF;
2258: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2259: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2260: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2261: l_party_id := H_SCORES.FIRST;
2262: WHILE l_party_id IS NOT NULL LOOP
2263: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2264: l_party_id:=H_SCORES.NEXT(l_party_id);

Line 2290: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN

2286: l_search_ctx_id,H_PARTY_ID(I));
2287: l_party_id_idx := H_PARTY_ID.COUNT+1;
2288: ELSE
2289: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2290: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2291: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2292: hz_utility_v2pub.debug(p_message=>'In eval party site level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2293: END IF;
2294: IF p_index>1 THEN

Line 2312: open_party_site_cursor(p_dup_party_id,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, p_search_merged, 'N','N',l_party_site_cur);

2308: END IF;
2309: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2310: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_PARTY_SITE_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2311: END IF;
2312: open_party_site_cursor(p_dup_party_id,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, p_search_merged, 'N','N',l_party_site_cur);
2313: LOOP
2314: FETCH l_party_site_cur INTO
2315: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
2316: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 2368: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS

2364: END IF;
2365: END;
2366:
2367: /** Private procedure to acquire and score at contact point level ***/
2368: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2369: l_party_id_idx NUMBER:=1;
2370: l_ctx_id NUMBER;
2371: h_ct_id HZ_PARTY_SEARCH.IDList;
2372: h_ct_party_id HZ_PARTY_SEARCH.IDList;

Line 2402: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN

2398: l_search_ctx_id,H_PARTY_ID(I));
2399: l_party_id_idx := H_PARTY_ID.COUNT+1;
2400: ELSE
2401: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2402: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2403: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2404: hz_utility_v2pub.debug(p_message=>'In eval contact level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2405: END IF;
2406: IF p_index>1 THEN

Line 2424: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);

2420: END IF;
2421: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2422: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_CONTACT_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2423: END IF;
2424: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2425: LOOP
2426: FETCH l_contact_cur INTO
2427: l_org_contact_id, l_ct_party_id ;
2428: EXIT WHEN l_contact_cur%NOTFOUND;

Line 2477: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS

2473: END IF;
2474: END;
2475:
2476: /** Private procedure to acquire and score at contact point level ***/
2477: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2478: l_party_id_idx NUMBER:=1;
2479: l_ctx_id NUMBER;
2480: h_cpt_id HZ_PARTY_SEARCH.IDList;
2481: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

Line 2513: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN

2509: l_search_ctx_id,H_PARTY_ID(I));
2510: l_party_id_idx := H_PARTY_ID.COUNT+1;
2511: ELSE
2512: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2513: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2514: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2515: hz_utility_v2pub.debug(p_message=>'In eval contact point level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2516: END IF;
2517: IF p_index>1 THEN

Line 2535: open_contact_pt_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, p_search_merged, 'N', 'N',l_contact_pt_cur);

2531: END IF;
2532: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2533: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_CONTACT_PT_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2534: END IF;
2535: open_contact_pt_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, p_search_merged, 'N', 'N',l_contact_pt_cur);
2536: LOOP
2537: FETCH l_contact_pt_cur INTO
2538: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
2539: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2635: IF l_party_contains_str IS NOT NULL THEN

2631: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2632: l_threshold := 55;
2633: other_acq_criteria_exists := TRUE ;
2634: --check if acquisition criteria exists for any other entity
2635: IF l_party_contains_str IS NOT NULL THEN
2636: acq_cnt := 1;
2637: END IF;
2638: IF l_party_site_contains_str IS NOT NULL THEN
2639: acq_cnt := acq_cnt+1;

Line 2638: IF l_party_site_contains_str IS NOT NULL THEN

2634: --check if acquisition criteria exists for any other entity
2635: IF l_party_contains_str IS NOT NULL THEN
2636: acq_cnt := 1;
2637: END IF;
2638: IF l_party_site_contains_str IS NOT NULL THEN
2639: acq_cnt := acq_cnt+1;
2640: END IF;
2641: IF l_contact_contains_str IS NOT NULL THEN
2642: acq_cnt := acq_cnt+1;

Line 2641: IF l_contact_contains_str IS NOT NULL THEN

2637: END IF;
2638: IF l_party_site_contains_str IS NOT NULL THEN
2639: acq_cnt := acq_cnt+1;
2640: END IF;
2641: IF l_contact_contains_str IS NOT NULL THEN
2642: acq_cnt := acq_cnt+1;
2643: END IF;
2644: IF l_contact_pt_contains_str IS NOT NULL THEN
2645: acq_cnt := acq_cnt+1;

Line 2644: IF l_contact_pt_contains_str IS NOT NULL THEN

2640: END IF;
2641: IF l_contact_contains_str IS NOT NULL THEN
2642: acq_cnt := acq_cnt+1;
2643: END IF;
2644: IF l_contact_pt_contains_str IS NOT NULL THEN
2645: acq_cnt := acq_cnt+1;
2646: END IF;
2647: IF acq_cnt>1 THEN
2648: other_acq_criteria_exists := TRUE;

Line 2678: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN

2674: FND_MSG_PUB.ADD;
2675: RAISE FND_API.G_EXC_ERROR;
2676: END IF;
2677: END IF;
2678: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2679: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2680: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2681: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2682: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

Line 2679: eval_party_level(l_party_contains_str,call_type(p_index), p_index);

2675: RAISE FND_API.G_EXC_ERROR;
2676: END IF;
2677: END IF;
2678: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2679: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2680: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2681: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2682: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2683: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

Line 2680: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN

2676: END IF;
2677: END IF;
2678: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2679: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2680: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2681: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2682: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2683: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2684: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

Line 2681: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

2677: END IF;
2678: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2679: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2680: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2681: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2682: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2683: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2684: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2685: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

Line 2682: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

2678: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2679: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2680: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2681: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2682: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2683: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2684: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2685: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2686: END IF;

Line 2683: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

2679: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2680: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2681: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2682: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2683: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2684: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2685: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2686: END IF;
2687: END;

Line 2684: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

2680: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2681: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2682: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2683: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2684: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2685: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2686: END IF;
2687: END;
2688: /************ End of find_parties private procedures **********/

Line 2685: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

2681: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2682: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2683: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2684: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2685: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2686: END IF;
2687: END;
2688: /************ End of find_parties private procedures **********/
2689:

Line 2768: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);

2764: hz_utility_v2pub.debug(p_message=>'Maximum records that will be evaluated is '||l_max_thresh,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2765: END IF;
2766: -- ************************************************************
2767: -- STEP 2. Setup of intermedia query strings for Acquisition query
2768: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2769: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2770: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2771: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2772: hz_utility_v2pub.debug(p_message=>'Commencing the DENORM LOGIC in find_parties',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 2769: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

2765: END IF;
2766: -- ************************************************************
2767: -- STEP 2. Setup of intermedia query strings for Acquisition query
2768: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2769: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2770: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2771: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2772: hz_utility_v2pub.debug(p_message=>'Commencing the DENORM LOGIC in find_parties',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2773: END IF;

Line 2770: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);

2766: -- ************************************************************
2767: -- STEP 2. Setup of intermedia query strings for Acquisition query
2768: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2769: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2770: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2771: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2772: hz_utility_v2pub.debug(p_message=>'Commencing the DENORM LOGIC in find_parties',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2773: END IF;
2774: l_denorm_max_score:=0;

Line 2802: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, l_denorm_str, l_party_max_score, l_denorm_max_score, l_non_denorm_max_score, round((55/250)*l_entered_max_score));

2798: END IF;
2799: ELSE
2800: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2801: END IF;
2802: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, l_denorm_str, l_party_max_score, l_denorm_max_score, l_non_denorm_max_score, round((55/250)*l_entered_max_score));
2803: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2804:
2805: -- Setup Search Context ID
2806: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2812: IF l_party_contains_str IS NULL THEN

2808:
2809: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2810: hz_utility_v2pub.debug(p_message=>'Search context id in find_parties is '||x_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2811: END IF;
2812: IF l_party_contains_str IS NULL THEN
2813: defpt := 1;
2814: END IF;
2815: IF l_party_site_contains_str IS NULL THEN
2816: defps := 1;

Line 2815: IF l_party_site_contains_str IS NULL THEN

2811: END IF;
2812: IF l_party_contains_str IS NULL THEN
2813: defpt := 1;
2814: END IF;
2815: IF l_party_site_contains_str IS NULL THEN
2816: defps := 1;
2817: END IF;
2818: IF l_contact_contains_str IS NULL THEN
2819: defct := 1;

Line 2818: IF l_contact_contains_str IS NULL THEN

2814: END IF;
2815: IF l_party_site_contains_str IS NULL THEN
2816: defps := 1;
2817: END IF;
2818: IF l_contact_contains_str IS NULL THEN
2819: defct := 1;
2820: END IF;
2821: IF l_contact_pt_contains_str IS NULL THEN
2822: defcpt := 1;

Line 2821: IF l_contact_pt_contains_str IS NULL THEN

2817: END IF;
2818: IF l_contact_contains_str IS NULL THEN
2819: defct := 1;
2820: END IF;
2821: IF l_contact_pt_contains_str IS NULL THEN
2822: defcpt := 1;
2823: END IF;
2824:
2825: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 2830: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

2826: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2827: hz_utility_v2pub.debug(p_message=>'------------------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2828: END IF;
2829: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2830: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2831: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2832: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2833: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2834: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 2831: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

2827: hz_utility_v2pub.debug(p_message=>'------------------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2828: END IF;
2829: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2830: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2831: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2832: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2833: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2834: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2835: END IF;

Line 2832: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

2828: END IF;
2829: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2830: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2831: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2832: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2833: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2834: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2835: END IF;
2836: FOR I in 1..5 LOOP

Line 2833: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

2829: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2830: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2831: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2832: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2833: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2834: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2835: END IF;
2836: FOR I in 1..5 LOOP
2837: do_eval(I);

Line 2947: l_party_contains_str VARCHAR2(32000);

2943: x_num_matches OUT NUMBER
2944: ) IS
2945:
2946: -- Strings to hold the generated Intermedia query strings
2947: l_party_contains_str VARCHAR2(32000);
2948: l_party_site_contains_str VARCHAR2(32000);
2949: l_contact_contains_str VARCHAR2(32000);
2950: l_contact_pt_contains_str VARCHAR2(32000);
2951: l_denorm_str VARCHAR2(32000);

Line 2948: l_party_site_contains_str VARCHAR2(32000);

2944: ) IS
2945:
2946: -- Strings to hold the generated Intermedia query strings
2947: l_party_contains_str VARCHAR2(32000);
2948: l_party_site_contains_str VARCHAR2(32000);
2949: l_contact_contains_str VARCHAR2(32000);
2950: l_contact_pt_contains_str VARCHAR2(32000);
2951: l_denorm_str VARCHAR2(32000);
2952: l_ps_denorm_str VARCHAR2(32000);

Line 2949: l_contact_contains_str VARCHAR2(32000);

2945:
2946: -- Strings to hold the generated Intermedia query strings
2947: l_party_contains_str VARCHAR2(32000);
2948: l_party_site_contains_str VARCHAR2(32000);
2949: l_contact_contains_str VARCHAR2(32000);
2950: l_contact_pt_contains_str VARCHAR2(32000);
2951: l_denorm_str VARCHAR2(32000);
2952: l_ps_denorm_str VARCHAR2(32000);
2953: l_ct_denorm_str VARCHAR2(32000);

Line 2950: l_contact_pt_contains_str VARCHAR2(32000);

2946: -- Strings to hold the generated Intermedia query strings
2947: l_party_contains_str VARCHAR2(32000);
2948: l_party_site_contains_str VARCHAR2(32000);
2949: l_contact_contains_str VARCHAR2(32000);
2950: l_contact_pt_contains_str VARCHAR2(32000);
2951: l_denorm_str VARCHAR2(32000);
2952: l_ps_denorm_str VARCHAR2(32000);
2953: l_ct_denorm_str VARCHAR2(32000);
2954: l_cpt_denorm_str VARCHAR2(32000);

Line 3071: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS

3067: call_type(2) := 'OR';
3068: END;
3069:
3070: /** Private procedure to acquire and score at party level ***/
3071: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3072: l_party_id_idx NUMBER:=1;
3073: l_ctx_id NUMBER;
3074: l_TX35_new varchar2(4000);
3075: BEGIN

Line 3093: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN

3089: l_search_ctx_id,H_PARTY_ID(I));
3090: l_party_id_idx := H_PARTY_ID.COUNT+1;
3091: ELSE
3092: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3093: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3094: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3095: hz_utility_v2pub.debug(p_message=>'In eval party level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3096: END IF;
3097: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3101: IF (l_party_site_contains_str IS NULL AND

3097: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3098: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3099: hz_utility_v2pub.debug(p_message=>'In eval party level resolution option is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3100: END IF;
3101: IF (l_party_site_contains_str IS NULL AND
3102: l_contact_contains_str IS NULL AND
3103: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3104: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3105: RAISE L_RETURN_IMM_EXC;

Line 3102: l_contact_contains_str IS NULL AND

3098: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3099: hz_utility_v2pub.debug(p_message=>'In eval party level resolution option is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3100: END IF;
3101: IF (l_party_site_contains_str IS NULL AND
3102: l_contact_contains_str IS NULL AND
3103: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3104: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3105: RAISE L_RETURN_IMM_EXC;
3106: ELSE

Line 3103: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN

3099: hz_utility_v2pub.debug(p_message=>'In eval party level resolution option is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3100: END IF;
3101: IF (l_party_site_contains_str IS NULL AND
3102: l_contact_contains_str IS NULL AND
3103: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3104: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3105: RAISE L_RETURN_IMM_EXC;
3106: ELSE
3107: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

Line 3107: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

3103: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3104: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3105: RAISE L_RETURN_IMM_EXC;
3106: ELSE
3107: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3108: END IF;
3109: ELSE
3110: IF p_index>1 THEN
3111: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 3127: open_party_cursor(p_dup_party_id, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,p_search_merged,l_party_cur);

3123: l_ctx_id := NULL;
3124: l_party_id_idx := H_PARTY_ID.COUNT+1;
3125: END IF;
3126: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3127: open_party_cursor(p_dup_party_id, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,p_search_merged,l_party_cur);
3128: END IF;
3129: LOOP
3130: FETCH l_party_cur INTO
3131: l_party_id , l_TX2, l_TX4, l_TX8, l_TX41, l_TX44;

Line 3149: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL

3145: l_party_id_idx:= l_party_id_idx+1;
3146: END IF;
3147: IF (l_party_id_idx-1)>l_max_thresh THEN
3148: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3149: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3150: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3151: AND l_contact_pt_contains_str IS NULL THEN
3152: H_PARTY_ID.DELETE(l_party_id_idx-1);
3153: H_PARTY_ID_LIST.DELETE(l_index);

Line 3151: AND l_contact_pt_contains_str IS NULL THEN

3147: IF (l_party_id_idx-1)>l_max_thresh THEN
3148: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3149: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3150: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3151: AND l_contact_pt_contains_str IS NULL THEN
3152: H_PARTY_ID.DELETE(l_party_id_idx-1);
3153: H_PARTY_ID_LIST.DELETE(l_index);
3154: H_SCORES.DELETE(l_index);
3155: EXIT;

Line 3180: p_contains_str VARCHAR2,

3176: CLOSE l_party_cur;
3177: ROLLBACK to eval_start;
3178: END;
3179: PROCEDURE open_person_contact_cursor(
3180: p_contains_str VARCHAR2,
3181: p_search_ctx_id NUMBER,
3182: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3183: BEGIN
3184: OPEN x_cursor FOR

Line 3187: WHERE contains( concat_col, p_contains_str)>0

3183: BEGIN
3184: OPEN x_cursor FOR
3185: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3186: FROM HZ_STAGED_CONTACTS stage
3187: WHERE contains( concat_col, p_contains_str)>0
3188: AND ORG_CONTACT_ID in (
3189: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3190: org_contact_id
3191: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

Line 3211: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS

3207: end if;
3208: END;
3209:
3210: /** Private procedure to acquire and score at party site level ***/
3211: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3212: l_party_id_idx NUMBER:=1;
3213: l_ctx_id NUMBER;
3214: h_ps_id HZ_PARTY_SEARCH.IDList;
3215: h_ps_party_id HZ_PARTY_SEARCH.IDList;

Line 3239: open_party_site_cursor(p_dup_party_id,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, p_search_merged,'N', 'Y',l_party_site_cur);

3235: ELSE
3236: l_party_id_idx := H_PARTY_ID.COUNT+1;
3237: l_ctx_id := NULL;
3238: END IF;
3239: open_party_site_cursor(p_dup_party_id,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, p_search_merged,'N', 'Y',l_party_site_cur);
3240: LOOP
3241: FETCH l_party_site_cur INTO
3242: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
3243: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 3310: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS

3306: END IF;
3307: END;
3308:
3309: /** Private procedure to acquire and score at party site level ***/
3310: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3311: l_party_id_idx NUMBER:=1;
3312: l_ctx_id NUMBER;
3313: h_ct_id HZ_PARTY_SEARCH.IDList;
3314: h_ct_party_id HZ_PARTY_SEARCH.IDList;

Line 3326: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);

3322: FORALL I in 1..H_PARTY_ID.COUNT
3323: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3324: l_search_ctx_id,H_PARTY_ID(I));
3325: l_party_id_idx := H_PARTY_ID.COUNT+1;
3326: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3327: LOOP
3328: FETCH l_contact_cur INTO
3329: l_org_contact_id, l_ct_party_id ;
3330: EXIT WHEN l_contact_cur%NOTFOUND;

Line 3368: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS

3364: END IF;
3365: END;
3366:
3367: /** Private procedure to acquire and score at contact point level ***/
3368: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3369: l_party_id_idx NUMBER:=1;
3370: l_ctx_id NUMBER;
3371: h_cpt_id HZ_PARTY_SEARCH.IDList;
3372: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

Line 3413: open_contact_pt_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, p_search_merged,'N', 'Y',l_contact_pt_cur);

3409: END IF;
3410: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3411: hz_utility_v2pub.debug(p_message=>'====== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_CONTACT_PT_CURSOR =======',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3412: END IF;
3413: open_contact_pt_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, p_search_merged,'N', 'Y',l_contact_pt_cur);
3414: LOOP
3415: FETCH l_contact_pt_cur INTO
3416: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3417: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3521: IF l_party_contains_str IS NOT NULL THEN

3517: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3518: l_threshold := 55;
3519: other_acq_criteria_exists := TRUE ;
3520: --check if acquisition criteria exists for any other entity
3521: IF l_party_contains_str IS NOT NULL THEN
3522: acq_cnt := 1;
3523: END IF;
3524: IF l_party_site_contains_str IS NOT NULL THEN
3525: acq_cnt := acq_cnt+1;

Line 3524: IF l_party_site_contains_str IS NOT NULL THEN

3520: --check if acquisition criteria exists for any other entity
3521: IF l_party_contains_str IS NOT NULL THEN
3522: acq_cnt := 1;
3523: END IF;
3524: IF l_party_site_contains_str IS NOT NULL THEN
3525: acq_cnt := acq_cnt+1;
3526: END IF;
3527: IF l_contact_contains_str IS NOT NULL THEN
3528: acq_cnt := acq_cnt+1;

Line 3527: IF l_contact_contains_str IS NOT NULL THEN

3523: END IF;
3524: IF l_party_site_contains_str IS NOT NULL THEN
3525: acq_cnt := acq_cnt+1;
3526: END IF;
3527: IF l_contact_contains_str IS NOT NULL THEN
3528: acq_cnt := acq_cnt+1;
3529: END IF;
3530: IF l_contact_pt_contains_str IS NOT NULL THEN
3531: acq_cnt := acq_cnt+1;

Line 3530: IF l_contact_pt_contains_str IS NOT NULL THEN

3526: END IF;
3527: IF l_contact_contains_str IS NOT NULL THEN
3528: acq_cnt := acq_cnt+1;
3529: END IF;
3530: IF l_contact_pt_contains_str IS NOT NULL THEN
3531: acq_cnt := acq_cnt+1;
3532: END IF;
3533: IF acq_cnt>1 THEN
3534: other_acq_criteria_exists := TRUE;

Line 3564: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN

3560: FND_MSG_PUB.ADD;
3561: RAISE FND_API.G_EXC_ERROR;
3562: END IF;
3563: END IF;
3564: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3565: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3566: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3567: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

Line 3565: eval_party_level(l_party_contains_str,call_type(p_index), p_index);

3561: RAISE FND_API.G_EXC_ERROR;
3562: END IF;
3563: END IF;
3564: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3565: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3566: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3567: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3569: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

Line 3566: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN

3562: END IF;
3563: END IF;
3564: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3565: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3566: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3567: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3569: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3570: END IF;

Line 3567: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

3563: END IF;
3564: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3565: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3566: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3567: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3569: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3570: END IF;
3571: END;

Line 3568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

3564: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3565: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3566: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3567: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3569: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3570: END IF;
3571: END;
3572: /************ End of find_persons private procedures **********/

Line 3569: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

3565: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3566: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3567: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3568: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3569: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3570: END IF;
3571: END;
3572: /************ End of find_persons private procedures **********/
3573:

Line 3604: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);

3600: g_score_until_thresh := false;
3601: END IF;
3602: -- ************************************************************
3603: -- STEP 2. Setup of intermedia query strings for Acquisition query
3604: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3605: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3606: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3607: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3608: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

Line 3605: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

3601: END IF;
3602: -- ************************************************************
3603: -- STEP 2. Setup of intermedia query strings for Acquisition query
3604: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3605: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3606: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3607: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3608: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3609:

Line 3606: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);

3602: -- ************************************************************
3603: -- STEP 2. Setup of intermedia query strings for Acquisition query
3604: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3605: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3606: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3607: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3608: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3609:
3610: -- Setup Search Context ID

Line 3607: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);

3603: -- STEP 2. Setup of intermedia query strings for Acquisition query
3604: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3605: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3606: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3607: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3608: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3609:
3610: -- Setup Search Context ID
3611: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3614: IF l_party_contains_str IS NULL THEN

3610: -- Setup Search Context ID
3611: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3612: x_search_ctx_id := l_search_ctx_id;
3613:
3614: IF l_party_contains_str IS NULL THEN
3615: defpt := 1;
3616: END IF;
3617: IF l_party_site_contains_str IS NULL THEN
3618: defps := 1;

Line 3617: IF l_party_site_contains_str IS NULL THEN

3613:
3614: IF l_party_contains_str IS NULL THEN
3615: defpt := 1;
3616: END IF;
3617: IF l_party_site_contains_str IS NULL THEN
3618: defps := 1;
3619: END IF;
3620: IF l_contact_contains_str IS NULL THEN
3621: defct := 1;

Line 3620: IF l_contact_contains_str IS NULL THEN

3616: END IF;
3617: IF l_party_site_contains_str IS NULL THEN
3618: defps := 1;
3619: END IF;
3620: IF l_contact_contains_str IS NULL THEN
3621: defct := 1;
3622: END IF;
3623: IF l_contact_pt_contains_str IS NULL THEN
3624: defcpt := 1;

Line 3623: IF l_contact_pt_contains_str IS NULL THEN

3619: END IF;
3620: IF l_contact_contains_str IS NULL THEN
3621: defct := 1;
3622: END IF;
3623: IF l_contact_pt_contains_str IS NULL THEN
3624: defcpt := 1;
3625: END IF;
3626:
3627: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 3629: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

3625: END IF;
3626:
3627: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3628: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3629: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3630: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3631: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3632: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3633: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 3630: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

3626:
3627: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3628: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3629: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3630: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3631: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3632: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3633: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3634: END IF;

Line 3631: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

3627: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3628: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3629: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3630: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3631: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3632: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3633: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3634: END IF;
3635: FOR I in 1..5 LOOP

Line 3632: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

3628: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3629: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3630: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3631: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3632: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3633: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3634: END IF;
3635: FOR I in 1..5 LOOP
3636: do_eval(I);

Line 3638: IF l_contact_contains_str IS NOT NULL THEN

3634: END IF;
3635: FOR I in 1..5 LOOP
3636: do_eval(I);
3637: END LOOP;
3638: IF l_contact_contains_str IS NOT NULL THEN
3639: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3640: END IF;
3641: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3642: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 22 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 3639: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);

3635: FOR I in 1..5 LOOP
3636: do_eval(I);
3637: END LOOP;
3638: IF l_contact_contains_str IS NOT NULL THEN
3639: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3640: END IF;
3641: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3642: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 22 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3643: END IF;

Line 4433: l_party_contains_str VARCHAR2(32000);

4429: x_num_matches OUT NUMBER
4430: ) IS
4431:
4432: -- Strings to hold the generated Intermedia query strings
4433: l_party_contains_str VARCHAR2(32000);
4434: l_match_idx NUMBER;
4435: l_party_site_contains_str VARCHAR2(32000);
4436: l_contact_contains_str VARCHAR2(32000);
4437: l_contact_pt_contains_str VARCHAR2(32000);

Line 4435: l_party_site_contains_str VARCHAR2(32000);

4431:
4432: -- Strings to hold the generated Intermedia query strings
4433: l_party_contains_str VARCHAR2(32000);
4434: l_match_idx NUMBER;
4435: l_party_site_contains_str VARCHAR2(32000);
4436: l_contact_contains_str VARCHAR2(32000);
4437: l_contact_pt_contains_str VARCHAR2(32000);
4438: l_tmp VARCHAR2(32000);
4439:

Line 4436: l_contact_contains_str VARCHAR2(32000);

4432: -- Strings to hold the generated Intermedia query strings
4433: l_party_contains_str VARCHAR2(32000);
4434: l_match_idx NUMBER;
4435: l_party_site_contains_str VARCHAR2(32000);
4436: l_contact_contains_str VARCHAR2(32000);
4437: l_contact_pt_contains_str VARCHAR2(32000);
4438: l_tmp VARCHAR2(32000);
4439:
4440: -- Other local variables

Line 4437: l_contact_pt_contains_str VARCHAR2(32000);

4433: l_party_contains_str VARCHAR2(32000);
4434: l_match_idx NUMBER;
4435: l_party_site_contains_str VARCHAR2(32000);
4436: l_contact_contains_str VARCHAR2(32000);
4437: l_contact_pt_contains_str VARCHAR2(32000);
4438: l_tmp VARCHAR2(32000);
4439:
4440: -- Other local variables
4441: l_match_str VARCHAR2(30); -- Match type (AND or OR)

Line 4512: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);

4508: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4509:
4510: -- ************************************************************
4511: -- STEP 2. Setup of intermedia query strings for Acquisition query
4512: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4513: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4514: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,p_party_site_list,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
4515:
4516: -- Setup Search Context ID

Line 4513: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);

4509:
4510: -- ************************************************************
4511: -- STEP 2. Setup of intermedia query strings for Acquisition query
4512: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4513: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4514: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,p_party_site_list,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
4515:
4516: -- Setup Search Context ID
4517: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 4522: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

4518: x_search_ctx_id := l_search_ctx_id;
4519:
4520: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4521: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4522: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4523: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4524: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4525: END IF;
4526: IF l_party_site_contains_str IS NULL THEN

Line 4523: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

4519:
4520: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4521: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4522: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4523: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4524: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4525: END IF;
4526: IF l_party_site_contains_str IS NULL THEN
4527: defps := 1;

Line 4526: IF l_party_site_contains_str IS NULL THEN

4522: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4523: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4524: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4525: END IF;
4526: IF l_party_site_contains_str IS NULL THEN
4527: defps := 1;
4528: END IF;
4529: IF l_contact_pt_contains_str IS NULL THEN
4530: defcpt := 1;

Line 4529: IF l_contact_pt_contains_str IS NULL THEN

4525: END IF;
4526: IF l_party_site_contains_str IS NULL THEN
4527: defps := 1;
4528: END IF;
4529: IF l_contact_pt_contains_str IS NULL THEN
4530: defcpt := 1;
4531: END IF;
4532:
4533: IF l_party_site_contains_str IS NOT NULL THEN

Line 4533: IF l_party_site_contains_str IS NOT NULL THEN

4529: IF l_contact_pt_contains_str IS NULL THEN
4530: defcpt := 1;
4531: END IF;
4532:
4533: IF l_party_site_contains_str IS NOT NULL THEN
4534: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4535: LOOP
4536: FETCH l_party_site_cur INTO
4537: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;

Line 4534: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);

4530: defcpt := 1;
4531: END IF;
4532:
4533: IF l_party_site_contains_str IS NOT NULL THEN
4534: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4535: LOOP
4536: FETCH l_party_site_cur INTO
4537: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
4538: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4557: IF l_contact_pt_contains_str IS NOT NULL THEN

4553: END LOOP;
4554: CLOSE l_party_site_cur;
4555: END IF;
4556:
4557: IF l_contact_pt_contains_str IS NOT NULL THEN
4558: SAVEPOINT eval_start;
4559: IF l_match_str = ' AND ' OR (
4560: ((l_cpt_max_score/l_entered_max_score)<(55/250))
4561: ) THEN

Line 4565: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'PARTY_SITES');

4561: ) THEN
4562: FORALL I in 1..l_match_ps_list.COUNT
4563: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4564: values (l_search_ctx_id,l_match_ps_list(I));
4565: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'PARTY_SITES');
4566: ELSE
4567: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4568: END IF;
4569: LOOP

Line 4567: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);

4563: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4564: values (l_search_ctx_id,l_match_ps_list(I));
4565: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'PARTY_SITES');
4566: ELSE
4567: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4568: END IF;
4569: LOOP
4570: FETCH l_contact_pt_cur INTO
4571: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4667: l_party_contains_str VARCHAR2(32000);

4663: x_num_matches OUT NUMBER
4664: ) IS
4665:
4666: -- Strings to hold the generated Intermedia query strings
4667: l_party_contains_str VARCHAR2(32000);
4668: l_party_site_contains_str VARCHAR2(32000);
4669: l_contact_contains_str VARCHAR2(32000);
4670: l_contact_pt_contains_str VARCHAR2(32000);
4671: l_tmp VARCHAR2(32000);

Line 4668: l_party_site_contains_str VARCHAR2(32000);

4664: ) IS
4665:
4666: -- Strings to hold the generated Intermedia query strings
4667: l_party_contains_str VARCHAR2(32000);
4668: l_party_site_contains_str VARCHAR2(32000);
4669: l_contact_contains_str VARCHAR2(32000);
4670: l_contact_pt_contains_str VARCHAR2(32000);
4671: l_tmp VARCHAR2(32000);
4672:

Line 4669: l_contact_contains_str VARCHAR2(32000);

4665:
4666: -- Strings to hold the generated Intermedia query strings
4667: l_party_contains_str VARCHAR2(32000);
4668: l_party_site_contains_str VARCHAR2(32000);
4669: l_contact_contains_str VARCHAR2(32000);
4670: l_contact_pt_contains_str VARCHAR2(32000);
4671: l_tmp VARCHAR2(32000);
4672:
4673: -- Other local variables

Line 4670: l_contact_pt_contains_str VARCHAR2(32000);

4666: -- Strings to hold the generated Intermedia query strings
4667: l_party_contains_str VARCHAR2(32000);
4668: l_party_site_contains_str VARCHAR2(32000);
4669: l_contact_contains_str VARCHAR2(32000);
4670: l_contact_pt_contains_str VARCHAR2(32000);
4671: l_tmp VARCHAR2(32000);
4672:
4673: -- Other local variables
4674: l_match_str VARCHAR2(30); -- Match type (AND or OR)

Line 4741: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);

4737: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4738:
4739: -- ************************************************************
4740: -- STEP 2. Setup of intermedia query strings for Acquisition query
4741: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4742: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4743: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,p_contact_list,p_contact_point_list);
4744:
4745: -- Setup Search Context ID

Line 4742: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);

4738:
4739: -- ************************************************************
4740: -- STEP 2. Setup of intermedia query strings for Acquisition query
4741: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4742: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4743: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,p_contact_list,p_contact_point_list);
4744:
4745: -- Setup Search Context ID
4746: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 4751: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

4747: x_search_ctx_id := l_search_ctx_id;
4748:
4749: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4750: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4751: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4752: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4753: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4754: END IF;
4755: IF l_contact_contains_str IS NULL THEN

Line 4752: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

4748:
4749: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4750: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4751: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4752: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4753: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4754: END IF;
4755: IF l_contact_contains_str IS NULL THEN
4756: defct := 1;

Line 4755: IF l_contact_contains_str IS NULL THEN

4751: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4752: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4753: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4754: END IF;
4755: IF l_contact_contains_str IS NULL THEN
4756: defct := 1;
4757: END IF;
4758: IF l_contact_pt_contains_str IS NULL THEN
4759: defcpt := 1;

Line 4758: IF l_contact_pt_contains_str IS NULL THEN

4754: END IF;
4755: IF l_contact_contains_str IS NULL THEN
4756: defct := 1;
4757: END IF;
4758: IF l_contact_pt_contains_str IS NULL THEN
4759: defcpt := 1;
4760: END IF;
4761:
4762: IF l_contact_contains_str IS NOT NULL THEN

Line 4762: IF l_contact_contains_str IS NOT NULL THEN

4758: IF l_contact_pt_contains_str IS NULL THEN
4759: defcpt := 1;
4760: END IF;
4761:
4762: IF l_contact_contains_str IS NOT NULL THEN
4763: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4764: LOOP
4765: FETCH l_contact_cur INTO
4766: l_org_contact_id, l_ct_party_id ;

Line 4763: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);

4759: defcpt := 1;
4760: END IF;
4761:
4762: IF l_contact_contains_str IS NOT NULL THEN
4763: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4764: LOOP
4765: FETCH l_contact_cur INTO
4766: l_org_contact_id, l_ct_party_id ;
4767: EXIT WHEN l_contact_cur%NOTFOUND;

Line 4784: IF l_contact_pt_contains_str IS NOT NULL THEN

4780: END LOOP;
4781: CLOSE l_contact_cur;
4782: END IF;
4783:
4784: IF l_contact_pt_contains_str IS NOT NULL THEN
4785: SAVEPOINT eval_start;
4786: IF l_match_str = ' AND ' OR (
4787: ((l_cpt_max_score/l_entered_max_score)<(55/250))
4788: ) THEN

Line 4792: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');

4788: ) THEN
4789: FORALL I in 1..l_match_contact_list.COUNT
4790: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4791: values (l_search_ctx_id,l_match_contact_list(I));
4792: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');
4793: ELSE
4794: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4795: END IF;
4796: LOOP

Line 4794: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);

4790: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4791: values (l_search_ctx_id,l_match_contact_list(I));
4792: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');
4793: ELSE
4794: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4795: END IF;
4796: LOOP
4797: FETCH l_contact_pt_cur INTO
4798: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4892: l_contact_pt_contains_str VARCHAR2(32000);

4888: ) IS
4889:
4890:
4891: -- Strings to hold the generated Intermedia query strings
4892: l_contact_pt_contains_str VARCHAR2(32000);
4893: l_tmp VARCHAR2(32000);
4894:
4895: -- Other local variables
4896: l_match_str VARCHAR2(30); -- Match type (AND or OR)

Line 4950: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);

4946: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4947:
4948: -- ************************************************************
4949: -- STEP 2. Setup of intermedia query strings for Acquisition query
4950: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4951: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
4952:
4953: -- Setup Search Context ID
4954: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 4958: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

4954: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4955: x_search_ctx_id := l_search_ctx_id;
4956: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4957: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4958: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4959: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4960: END IF;
4961:
4962: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 4962: IF l_contact_pt_contains_str IS NOT NULL THEN

4958: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4959: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4960: END IF;
4961:
4962: IF l_contact_pt_contains_str IS NOT NULL THEN
4963: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4964: cnt := 1;
4965: LOOP
4966: FETCH l_contact_pt_cur INTO

Line 4963: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);

4959: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4960: END IF;
4961:
4962: IF l_contact_pt_contains_str IS NOT NULL THEN
4963: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4964: cnt := 1;
4965: LOOP
4966: FETCH l_contact_pt_cur INTO
4967: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 5034: l_party_contains_str VARCHAR2(32000);

5030: x_search_ctx_id IN OUT NUMBER
5031: ) IS
5032:
5033: -- Strings to hold the generated Intermedia query strings
5034: l_party_contains_str VARCHAR2(32000);
5035: l_party_site_contains_str VARCHAR2(32000);
5036: l_contact_contains_str VARCHAR2(32000);
5037: l_contact_pt_contains_str VARCHAR2(32000);
5038: l_tmp VARCHAR2(32000);

Line 5035: l_party_site_contains_str VARCHAR2(32000);

5031: ) IS
5032:
5033: -- Strings to hold the generated Intermedia query strings
5034: l_party_contains_str VARCHAR2(32000);
5035: l_party_site_contains_str VARCHAR2(32000);
5036: l_contact_contains_str VARCHAR2(32000);
5037: l_contact_pt_contains_str VARCHAR2(32000);
5038: l_tmp VARCHAR2(32000);
5039:

Line 5036: l_contact_contains_str VARCHAR2(32000);

5032:
5033: -- Strings to hold the generated Intermedia query strings
5034: l_party_contains_str VARCHAR2(32000);
5035: l_party_site_contains_str VARCHAR2(32000);
5036: l_contact_contains_str VARCHAR2(32000);
5037: l_contact_pt_contains_str VARCHAR2(32000);
5038: l_tmp VARCHAR2(32000);
5039:
5040: -- Other local variables

Line 5037: l_contact_pt_contains_str VARCHAR2(32000);

5033: -- Strings to hold the generated Intermedia query strings
5034: l_party_contains_str VARCHAR2(32000);
5035: l_party_site_contains_str VARCHAR2(32000);
5036: l_contact_contains_str VARCHAR2(32000);
5037: l_contact_pt_contains_str VARCHAR2(32000);
5038: l_tmp VARCHAR2(32000);
5039:
5040: -- Other local variables
5041: l_match_str VARCHAR2(30); -- Match type (AND or OR)

Line 5107: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);

5103: g_score_until_thresh := false;
5104: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5105: -- ************************************************************
5106: -- STEP 2. Setup of intermedia query strings for Acquisition query
5107: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5108: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5109: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5110: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5111:

Line 5108: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);

5104: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5105: -- ************************************************************
5106: -- STEP 2. Setup of intermedia query strings for Acquisition query
5107: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5108: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5109: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5110: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5111:
5112: -- Setup Search Context ID

Line 5109: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);

5105: -- ************************************************************
5106: -- STEP 2. Setup of intermedia query strings for Acquisition query
5107: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5108: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5109: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5110: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5111:
5112: -- Setup Search Context ID
5113: IF x_search_ctx_id IS NULL THEN

Line 5129: IF l_party_site_contains_str IS NOT NULL THEN

5125: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX2,l_TX4,l_TX8,l_TX41,l_TX44,1);
5126: END LOOP;
5127: CLOSE l_party_cur;
5128:
5129: IF l_party_site_contains_str IS NOT NULL THEN
5130: l_max_score := 0;
5131: l_max_id := 0;
5132: l_max_idx := 0;
5133: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

Line 5134: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);

5130: l_max_score := 0;
5131: l_max_id := 0;
5132: l_max_idx := 0;
5133: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5134: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5135: ELSE
5136: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5137: END IF;
5138: LOOP

Line 5136: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);

5132: l_max_idx := 0;
5133: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5134: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5135: ELSE
5136: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5137: END IF;
5138: LOOP
5139: FETCH l_party_site_cur INTO
5140: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX4, l_TX3;

Line 5157: IF l_contact_contains_str IS NOT NULL THEN

5153: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX4,l_max_TX3,l_max_idx);
5154: END IF;
5155: END IF;
5156:
5157: IF l_contact_contains_str IS NOT NULL THEN
5158: l_max_score := 0;
5159: l_max_id := 0;
5160: l_max_idx := 0;
5161: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

Line 5161: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

5157: IF l_contact_contains_str IS NOT NULL THEN
5158: l_max_score := 0;
5159: l_max_id := 0;
5160: l_max_idx := 0;
5161: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5162: LOOP
5163: FETCH l_contact_cur INTO
5164: l_org_contact_id, l_ct_party_id ;
5165: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5179: IF l_contact_pt_contains_str IS NOT NULL THEN

5175: INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_idx);
5176: END IF;
5177: END IF;
5178:
5179: IF l_contact_pt_contains_str IS NOT NULL THEN
5180: l_max_score := 0;
5181: l_max_id := 0;
5182: l_max_idx := 0;
5183: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

Line 5184: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);

5180: l_max_score := 0;
5181: l_max_id := 0;
5182: l_max_idx := 0;
5183: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5184: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5185: ELSE
5186: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5187: END IF;
5188: LOOP

Line 5186: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);

5182: l_max_idx := 0;
5183: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5184: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5185: ELSE
5186: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5187: END IF;
5188: LOOP
5189: FETCH l_contact_pt_cur INTO
5190: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;