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 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 1225: ' WHERE contains( concat_col, :cont)>0'||

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

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

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

Line 1239: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

Line 1270: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 1449: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 1450: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 1464: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1481: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1515: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

Line 1629: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1662: p_contains_str VARCHAR2,

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

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

1688: THEN
1689: OPEN x_cursor FOR
1690: 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
1691: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1692: WHERE contains( concat_col, p_contains_str)>0
1693: AND d.search_context_id = p_search_ctx_id
1694: AND( (l_search_merged ='Y' )
1695: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1696: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1700: THEN
1701: OPEN x_cursor FOR
1702: 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
1703: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1704: WHERE contains( concat_col, p_contains_str)>0
1705: AND d.search_context_id = p_search_ctx_id
1706: AND( (l_search_merged ='Y' )
1707: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1708: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

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

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

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

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

1734: AND stage.party_id = p_party_id
1735: UNION
1736: 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
1737: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1738: WHERE contains( concat_col, p_contains_str)>0
1739: AND r.object_id = p_party_id
1740: AND( (l_search_merged ='Y' )
1741: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1742: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

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

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

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

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

1786: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1787: UNION
1788: 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
1789: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1790: WHERE contains( concat_col, p_contains_str)>0
1791: AND d.search_context_id = p_search_ctx_id
1792: AND d.party_id = r.subject_id
1793: AND r.relationship_id = oc.party_relationship_id
1794: AND oc.org_contact_id = stage.org_contact_id

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

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

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

1844: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1845: END IF;
1846: 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 '||
1847: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1848: ' WHERE contains( concat_col, :cont)>0 '||
1849: ' AND (stage.org_contact_id is null '||
1850: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1851: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1852: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

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

Line 1868: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 1869: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 1883: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1900: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 2028: l_party_contains_str VARCHAR2(32000);

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

Line 2029: l_party_site_contains_str VARCHAR2(32000);

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

Line 2030: l_contact_contains_str VARCHAR2(32000);

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

Line 2031: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

2149: l_search_ctx_id,H_PARTY_ID(I));
2150: l_party_id_idx := H_PARTY_ID.COUNT+1;
2151: ELSE
2152: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2153: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2154: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2155: 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);
2156: END IF;
2157: 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 2161: IF (l_party_site_contains_str IS NULL AND

2157: 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
2158: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2159: 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);
2160: END IF;
2161: IF (l_party_site_contains_str IS NULL AND
2162: l_contact_contains_str IS NULL AND
2163: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2164: 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);
2165: RAISE L_RETURN_IMM_EXC;

Line 2162: l_contact_contains_str IS NULL AND

2158: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2159: 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);
2160: END IF;
2161: IF (l_party_site_contains_str IS NULL AND
2162: l_contact_contains_str IS NULL AND
2163: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2164: 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);
2165: RAISE L_RETURN_IMM_EXC;
2166: ELSE

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

2159: 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);
2160: END IF;
2161: IF (l_party_site_contains_str IS NULL AND
2162: l_contact_contains_str IS NULL AND
2163: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2164: 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);
2165: RAISE L_RETURN_IMM_EXC;
2166: ELSE
2167: 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 2167: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2163: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2164: 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);
2165: RAISE L_RETURN_IMM_EXC;
2166: ELSE
2167: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2168: END IF;
2169: ELSE
2170: IF p_index>1 THEN
2171: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2186: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2187: 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);
2188: END IF;
2189: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2190: 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);
2191: END IF;
2192: LOOP
2193: FETCH l_party_cur INTO
2194: l_party_id , l_TX2, l_TX4, l_TX8, l_TX41, l_TX44;

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

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

Line 2214: AND l_contact_pt_contains_str IS NULL THEN

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

Line 2243: 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

2239: ROLLBACK to eval_start;
2240: END;
2241:
2242: /** Private procedure to acquire and score at party site level ***/
2243: 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
2244: l_party_id_idx NUMBER:=1;
2245: l_ctx_id NUMBER;
2246: h_ps_id HZ_PARTY_SEARCH.IDList;
2247: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2251: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2252: 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);
2253: 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);
2254: END IF;
2255: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2256: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2257: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2258: l_party_id := H_SCORES.FIRST;
2259: WHILE l_party_id IS NOT NULL LOOP

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

2252: 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);
2253: 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);
2254: END IF;
2255: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2256: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2257: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2258: l_party_id := H_SCORES.FIRST;
2259: WHILE l_party_id IS NOT NULL LOOP
2260: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2253: 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);
2254: END IF;
2255: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2256: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2257: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2258: l_party_id := H_SCORES.FIRST;
2259: WHILE l_party_id IS NOT NULL LOOP
2260: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2261: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2283: l_search_ctx_id,H_PARTY_ID(I));
2284: l_party_id_idx := H_PARTY_ID.COUNT+1;
2285: ELSE
2286: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2287: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2288: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2289: 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);
2290: END IF;
2291: IF p_index>1 THEN

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

2305: END IF;
2306: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2307: 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);
2308: END IF;
2309: 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);
2310: LOOP
2311: FETCH l_party_site_cur INTO
2312: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
2313: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 2365: 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

2361: END IF;
2362: END;
2363:
2364: /** Private procedure to acquire and score at contact point level ***/
2365: 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
2366: l_party_id_idx NUMBER:=1;
2367: l_ctx_id NUMBER;
2368: h_ct_id HZ_PARTY_SEARCH.IDList;
2369: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2395: l_search_ctx_id,H_PARTY_ID(I));
2396: l_party_id_idx := H_PARTY_ID.COUNT+1;
2397: ELSE
2398: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2399: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2400: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2401: 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);
2402: END IF;
2403: IF p_index>1 THEN

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

2417: END IF;
2418: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2419: 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);
2420: END IF;
2421: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2422: LOOP
2423: FETCH l_contact_cur INTO
2424: l_org_contact_id, l_ct_party_id ;
2425: EXIT WHEN l_contact_cur%NOTFOUND;

Line 2474: 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

2470: END IF;
2471: END;
2472:
2473: /** Private procedure to acquire and score at contact point level ***/
2474: 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
2475: l_party_id_idx NUMBER:=1;
2476: l_ctx_id NUMBER;
2477: h_cpt_id HZ_PARTY_SEARCH.IDList;
2478: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2506: l_search_ctx_id,H_PARTY_ID(I));
2507: l_party_id_idx := H_PARTY_ID.COUNT+1;
2508: ELSE
2509: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2510: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2511: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2512: 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);
2513: END IF;
2514: IF p_index>1 THEN

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

2528: END IF;
2529: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2530: 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);
2531: END IF;
2532: 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);
2533: LOOP
2534: FETCH l_contact_pt_cur INTO
2535: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
2536: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2632: IF l_party_contains_str IS NOT NULL THEN

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

Line 2635: IF l_party_site_contains_str IS NOT NULL THEN

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

Line 2638: IF l_contact_contains_str IS NOT NULL THEN

2634: END IF;
2635: IF l_party_site_contains_str IS NOT NULL THEN
2636: acq_cnt := acq_cnt+1;
2637: END IF;
2638: IF l_contact_contains_str IS NOT NULL THEN
2639: acq_cnt := acq_cnt+1;
2640: END IF;
2641: IF l_contact_pt_contains_str IS NOT NULL THEN
2642: acq_cnt := acq_cnt+1;

Line 2641: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2761: 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);
2762: END IF;
2763: -- ************************************************************
2764: -- STEP 2. Setup of intermedia query strings for Acquisition query
2765: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2766: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2767: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2768: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2769: 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 2766: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

2762: END IF;
2763: -- ************************************************************
2764: -- STEP 2. Setup of intermedia query strings for Acquisition query
2765: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2766: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2767: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2768: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2769: 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);
2770: END IF;

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

2763: -- ************************************************************
2764: -- STEP 2. Setup of intermedia query strings for Acquisition query
2765: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2766: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2767: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2768: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2769: 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);
2770: END IF;
2771: l_denorm_max_score:=0;

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

2795: END IF;
2796: ELSE
2797: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2798: END IF;
2799: 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));
2800: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2801:
2802: -- Setup Search Context ID
2803: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2809: IF l_party_contains_str IS NULL THEN

2805:
2806: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2807: 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);
2808: END IF;
2809: IF l_party_contains_str IS NULL THEN
2810: defpt := 1;
2811: END IF;
2812: IF l_party_site_contains_str IS NULL THEN
2813: defps := 1;

Line 2812: IF l_party_site_contains_str IS NULL THEN

2808: END IF;
2809: IF l_party_contains_str IS NULL THEN
2810: defpt := 1;
2811: END IF;
2812: IF l_party_site_contains_str IS NULL THEN
2813: defps := 1;
2814: END IF;
2815: IF l_contact_contains_str IS NULL THEN
2816: defct := 1;

Line 2815: IF l_contact_contains_str IS NULL THEN

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

Line 2818: IF l_contact_pt_contains_str IS NULL THEN

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

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

2823: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2824: 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);
2825: END IF;
2826: 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);
2827: 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);
2828: 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);
2829: 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);
2830: 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);
2831: 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 2828: 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);

2824: 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);
2825: END IF;
2826: 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);
2827: 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);
2828: 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);
2829: 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);
2830: 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);
2831: 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);
2832: END IF;

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

2825: END IF;
2826: 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);
2827: 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);
2828: 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);
2829: 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);
2830: 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);
2831: 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);
2832: END IF;
2833: FOR I in 1..5 LOOP

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

2826: 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);
2827: 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);
2828: 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);
2829: 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);
2830: 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);
2831: 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);
2832: END IF;
2833: FOR I in 1..5 LOOP
2834: do_eval(I);

Line 2944: l_party_contains_str VARCHAR2(32000);

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

Line 2945: l_party_site_contains_str VARCHAR2(32000);

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

Line 2946: l_contact_contains_str VARCHAR2(32000);

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

Line 2947: l_contact_pt_contains_str VARCHAR2(32000);

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

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

3064: call_type(2) := 'OR';
3065: END;
3066:
3067: /** Private procedure to acquire and score at party level ***/
3068: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3069: l_party_id_idx NUMBER:=1;
3070: l_ctx_id NUMBER;
3071: BEGIN
3072: SAVEPOINT eval_start;

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

3085: l_search_ctx_id,H_PARTY_ID(I));
3086: l_party_id_idx := H_PARTY_ID.COUNT+1;
3087: ELSE
3088: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3089: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3090: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3091: 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);
3092: END IF;
3093: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3097: IF (l_party_site_contains_str IS NULL AND

3093: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' 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 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);
3096: END IF;
3097: IF (l_party_site_contains_str IS NULL AND
3098: l_contact_contains_str IS NULL AND
3099: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3100: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3101: RAISE L_RETURN_IMM_EXC;

Line 3098: l_contact_contains_str IS NULL AND

3094: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3095: 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);
3096: END IF;
3097: IF (l_party_site_contains_str IS NULL AND
3098: l_contact_contains_str IS NULL AND
3099: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3100: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3101: RAISE L_RETURN_IMM_EXC;
3102: ELSE

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

3095: 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);
3096: END IF;
3097: IF (l_party_site_contains_str IS NULL AND
3098: l_contact_contains_str IS NULL AND
3099: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3100: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3101: RAISE L_RETURN_IMM_EXC;
3102: ELSE
3103: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

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

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

3119: l_ctx_id := NULL;
3120: l_party_id_idx := H_PARTY_ID.COUNT+1;
3121: END IF;
3122: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3123: 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);
3124: END IF;
3125: LOOP
3126: FETCH l_party_cur INTO
3127: l_party_id , l_TX2, l_TX4, l_TX8, l_TX41, l_TX44;

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

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

Line 3147: AND l_contact_pt_contains_str IS NULL THEN

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

Line 3176: p_contains_str VARCHAR2,

3172: CLOSE l_party_cur;
3173: ROLLBACK to eval_start;
3174: END;
3175: PROCEDURE open_person_contact_cursor(
3176: p_contains_str VARCHAR2,
3177: p_search_ctx_id NUMBER,
3178: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3179: BEGIN
3180: OPEN x_cursor FOR

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

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

Line 3207: 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

3203: end if;
3204: END;
3205:
3206: /** Private procedure to acquire and score at party site level ***/
3207: 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
3208: l_party_id_idx NUMBER:=1;
3209: l_ctx_id NUMBER;
3210: h_ps_id HZ_PARTY_SEARCH.IDList;
3211: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3231: ELSE
3232: l_party_id_idx := H_PARTY_ID.COUNT+1;
3233: l_ctx_id := NULL;
3234: END IF;
3235: 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);
3236: LOOP
3237: FETCH l_party_site_cur INTO
3238: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
3239: EXIT WHEN l_party_site_cur%NOTFOUND;

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

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

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

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

Line 3364: 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

3360: END IF;
3361: END;
3362:
3363: /** Private procedure to acquire and score at contact point level ***/
3364: 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
3365: l_party_id_idx NUMBER:=1;
3366: l_ctx_id NUMBER;
3367: h_cpt_id HZ_PARTY_SEARCH.IDList;
3368: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3405: END IF;
3406: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3407: 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);
3408: END IF;
3409: 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);
3410: LOOP
3411: FETCH l_contact_pt_cur INTO
3412: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3413: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3517: IF l_party_contains_str IS NOT NULL THEN

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

Line 3520: IF l_party_site_contains_str IS NOT NULL THEN

3516: --check if acquisition criteria exists for any other entity
3517: IF l_party_contains_str IS NOT NULL THEN
3518: acq_cnt := 1;
3519: END IF;
3520: IF l_party_site_contains_str IS NOT NULL THEN
3521: acq_cnt := acq_cnt+1;
3522: END IF;
3523: IF l_contact_contains_str IS NOT NULL THEN
3524: acq_cnt := acq_cnt+1;

Line 3523: IF l_contact_contains_str IS NOT NULL THEN

3519: END IF;
3520: IF l_party_site_contains_str IS NOT NULL THEN
3521: acq_cnt := acq_cnt+1;
3522: END IF;
3523: IF l_contact_contains_str IS NOT NULL THEN
3524: acq_cnt := acq_cnt+1;
3525: END IF;
3526: IF l_contact_pt_contains_str IS NOT NULL THEN
3527: acq_cnt := acq_cnt+1;

Line 3526: IF l_contact_pt_contains_str IS NOT NULL THEN

3522: END IF;
3523: IF l_contact_contains_str IS NOT NULL THEN
3524: acq_cnt := acq_cnt+1;
3525: END IF;
3526: IF l_contact_pt_contains_str IS NOT NULL THEN
3527: acq_cnt := acq_cnt+1;
3528: END IF;
3529: IF acq_cnt>1 THEN
3530: other_acq_criteria_exists := TRUE;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 3610: IF l_party_contains_str IS NULL THEN

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

Line 3613: IF l_party_site_contains_str IS NULL THEN

3609:
3610: IF l_party_contains_str IS NULL THEN
3611: defpt := 1;
3612: END IF;
3613: IF l_party_site_contains_str IS NULL THEN
3614: defps := 1;
3615: END IF;
3616: IF l_contact_contains_str IS NULL THEN
3617: defct := 1;

Line 3616: IF l_contact_contains_str IS NULL THEN

3612: END IF;
3613: IF l_party_site_contains_str IS NULL THEN
3614: defps := 1;
3615: END IF;
3616: IF l_contact_contains_str IS NULL THEN
3617: defct := 1;
3618: END IF;
3619: IF l_contact_pt_contains_str IS NULL THEN
3620: defcpt := 1;

Line 3619: IF l_contact_pt_contains_str IS NULL THEN

3615: END IF;
3616: IF l_contact_contains_str IS NULL THEN
3617: defct := 1;
3618: END IF;
3619: IF l_contact_pt_contains_str IS NULL THEN
3620: defcpt := 1;
3621: END IF;
3622:
3623: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3621: END IF;
3622:
3623: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3624: 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);
3625: 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);
3626: 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);
3627: 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);
3628: 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);
3629: 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 3626: 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);

3622:
3623: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3624: 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);
3625: 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);
3626: 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);
3627: 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);
3628: 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);
3629: 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);
3630: END IF;

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

3623: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3624: 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);
3625: 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);
3626: 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);
3627: 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);
3628: 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);
3629: 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);
3630: END IF;
3631: FOR I in 1..5 LOOP

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

3624: 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);
3625: 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);
3626: 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);
3627: 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);
3628: 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);
3629: 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);
3630: END IF;
3631: FOR I in 1..5 LOOP
3632: do_eval(I);

Line 3634: IF l_contact_contains_str IS NOT NULL THEN

3630: END IF;
3631: FOR I in 1..5 LOOP
3632: do_eval(I);
3633: END LOOP;
3634: IF l_contact_contains_str IS NOT NULL THEN
3635: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3636: END IF;
3637: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3638: 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 3635: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);

3631: FOR I in 1..5 LOOP
3632: do_eval(I);
3633: END LOOP;
3634: IF l_contact_contains_str IS NOT NULL THEN
3635: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3636: END IF;
3637: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3638: 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);
3639: END IF;

Line 4429: l_party_contains_str VARCHAR2(32000);

4425: x_num_matches OUT NUMBER
4426: ) IS
4427:
4428: -- Strings to hold the generated Intermedia query strings
4429: l_party_contains_str VARCHAR2(32000);
4430: l_match_idx NUMBER;
4431: l_party_site_contains_str VARCHAR2(32000);
4432: l_contact_contains_str VARCHAR2(32000);
4433: l_contact_pt_contains_str VARCHAR2(32000);

Line 4431: l_party_site_contains_str VARCHAR2(32000);

4427:
4428: -- Strings to hold the generated Intermedia query strings
4429: l_party_contains_str VARCHAR2(32000);
4430: l_match_idx NUMBER;
4431: l_party_site_contains_str VARCHAR2(32000);
4432: l_contact_contains_str VARCHAR2(32000);
4433: l_contact_pt_contains_str VARCHAR2(32000);
4434: l_tmp VARCHAR2(32000);
4435:

Line 4432: l_contact_contains_str VARCHAR2(32000);

4428: -- Strings to hold the generated Intermedia query strings
4429: l_party_contains_str VARCHAR2(32000);
4430: l_match_idx NUMBER;
4431: l_party_site_contains_str VARCHAR2(32000);
4432: l_contact_contains_str VARCHAR2(32000);
4433: l_contact_pt_contains_str VARCHAR2(32000);
4434: l_tmp VARCHAR2(32000);
4435:
4436: -- Other local variables

Line 4433: l_contact_pt_contains_str VARCHAR2(32000);

4429: l_party_contains_str VARCHAR2(32000);
4430: l_match_idx NUMBER;
4431: l_party_site_contains_str VARCHAR2(32000);
4432: l_contact_contains_str VARCHAR2(32000);
4433: l_contact_pt_contains_str VARCHAR2(32000);
4434: l_tmp VARCHAR2(32000);
4435:
4436: -- Other local variables
4437: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4504: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4505:
4506: -- ************************************************************
4507: -- STEP 2. Setup of intermedia query strings for Acquisition query
4508: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4509: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4510: 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);
4511:
4512: -- Setup Search Context ID

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

4505:
4506: -- ************************************************************
4507: -- STEP 2. Setup of intermedia query strings for Acquisition query
4508: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4509: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4510: 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);
4511:
4512: -- Setup Search Context ID
4513: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4514: x_search_ctx_id := l_search_ctx_id;
4515:
4516: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4517: 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);
4518: 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);
4519: 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);
4520: 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);
4521: END IF;
4522: IF l_party_site_contains_str IS NULL THEN

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

4515:
4516: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4517: 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);
4518: 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);
4519: 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);
4520: 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);
4521: END IF;
4522: IF l_party_site_contains_str IS NULL THEN
4523: defps := 1;

Line 4522: IF l_party_site_contains_str IS NULL THEN

4518: 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);
4519: 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);
4520: 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);
4521: END IF;
4522: IF l_party_site_contains_str IS NULL THEN
4523: defps := 1;
4524: END IF;
4525: IF l_contact_pt_contains_str IS NULL THEN
4526: defcpt := 1;

Line 4525: IF l_contact_pt_contains_str IS NULL THEN

4521: END IF;
4522: IF l_party_site_contains_str IS NULL THEN
4523: defps := 1;
4524: END IF;
4525: IF l_contact_pt_contains_str IS NULL THEN
4526: defcpt := 1;
4527: END IF;
4528:
4529: IF l_party_site_contains_str IS NOT NULL THEN

Line 4529: IF l_party_site_contains_str IS NOT NULL THEN

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

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

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

Line 4553: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

4557: ) THEN
4558: FORALL I in 1..l_match_ps_list.COUNT
4559: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4560: values (l_search_ctx_id,l_match_ps_list(I));
4561: 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');
4562: ELSE
4563: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4564: END IF;
4565: LOOP

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

4559: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4560: values (l_search_ctx_id,l_match_ps_list(I));
4561: 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');
4562: ELSE
4563: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4564: END IF;
4565: LOOP
4566: FETCH l_contact_pt_cur INTO
4567: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4663: l_party_contains_str VARCHAR2(32000);

4659: x_num_matches OUT NUMBER
4660: ) IS
4661:
4662: -- Strings to hold the generated Intermedia query strings
4663: l_party_contains_str VARCHAR2(32000);
4664: l_party_site_contains_str VARCHAR2(32000);
4665: l_contact_contains_str VARCHAR2(32000);
4666: l_contact_pt_contains_str VARCHAR2(32000);
4667: l_tmp VARCHAR2(32000);

Line 4664: l_party_site_contains_str VARCHAR2(32000);

4660: ) IS
4661:
4662: -- Strings to hold the generated Intermedia query strings
4663: l_party_contains_str VARCHAR2(32000);
4664: l_party_site_contains_str VARCHAR2(32000);
4665: l_contact_contains_str VARCHAR2(32000);
4666: l_contact_pt_contains_str VARCHAR2(32000);
4667: l_tmp VARCHAR2(32000);
4668:

Line 4665: l_contact_contains_str VARCHAR2(32000);

4661:
4662: -- Strings to hold the generated Intermedia query strings
4663: l_party_contains_str VARCHAR2(32000);
4664: l_party_site_contains_str VARCHAR2(32000);
4665: l_contact_contains_str VARCHAR2(32000);
4666: l_contact_pt_contains_str VARCHAR2(32000);
4667: l_tmp VARCHAR2(32000);
4668:
4669: -- Other local variables

Line 4666: l_contact_pt_contains_str VARCHAR2(32000);

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

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

4733: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4734:
4735: -- ************************************************************
4736: -- STEP 2. Setup of intermedia query strings for Acquisition query
4737: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4738: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4739: 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);
4740:
4741: -- Setup Search Context ID

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

4734:
4735: -- ************************************************************
4736: -- STEP 2. Setup of intermedia query strings for Acquisition query
4737: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4738: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4739: 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);
4740:
4741: -- Setup Search Context ID
4742: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4743: x_search_ctx_id := l_search_ctx_id;
4744:
4745: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4746: 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);
4747: 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);
4748: 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);
4749: 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);
4750: END IF;
4751: IF l_contact_contains_str IS NULL THEN

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

4744:
4745: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4746: 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);
4747: 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);
4748: 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);
4749: 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);
4750: END IF;
4751: IF l_contact_contains_str IS NULL THEN
4752: defct := 1;

Line 4751: IF l_contact_contains_str IS NULL THEN

4747: 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);
4748: 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);
4749: 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);
4750: END IF;
4751: IF l_contact_contains_str IS NULL THEN
4752: defct := 1;
4753: END IF;
4754: IF l_contact_pt_contains_str IS NULL THEN
4755: defcpt := 1;

Line 4754: IF l_contact_pt_contains_str IS NULL THEN

4750: END IF;
4751: IF l_contact_contains_str IS NULL THEN
4752: defct := 1;
4753: END IF;
4754: IF l_contact_pt_contains_str IS NULL THEN
4755: defcpt := 1;
4756: END IF;
4757:
4758: IF l_contact_contains_str IS NOT NULL THEN

Line 4758: IF l_contact_contains_str IS NOT NULL THEN

4754: IF l_contact_pt_contains_str IS NULL THEN
4755: defcpt := 1;
4756: END IF;
4757:
4758: IF l_contact_contains_str IS NOT NULL THEN
4759: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4760: LOOP
4761: FETCH l_contact_cur INTO
4762: l_org_contact_id, l_ct_party_id ;

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

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

Line 4780: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

4784: ) THEN
4785: FORALL I in 1..l_match_contact_list.COUNT
4786: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4787: values (l_search_ctx_id,l_match_contact_list(I));
4788: 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');
4789: ELSE
4790: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4791: END IF;
4792: LOOP

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

4786: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4787: values (l_search_ctx_id,l_match_contact_list(I));
4788: 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');
4789: ELSE
4790: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4791: END IF;
4792: LOOP
4793: FETCH l_contact_pt_cur INTO
4794: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4888: l_contact_pt_contains_str VARCHAR2(32000);

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

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

4942: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4943:
4944: -- ************************************************************
4945: -- STEP 2. Setup of intermedia query strings for Acquisition query
4946: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4947: 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);
4948:
4949: -- Setup Search Context ID
4950: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4950: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4951: x_search_ctx_id := l_search_ctx_id;
4952: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4953: 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);
4954: 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);
4955: 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);
4956: END IF;
4957:
4958: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 4958: IF l_contact_pt_contains_str IS NOT NULL THEN

4954: 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);
4955: 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);
4956: END IF;
4957:
4958: IF l_contact_pt_contains_str IS NOT NULL THEN
4959: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4960: cnt := 1;
4961: LOOP
4962: FETCH l_contact_pt_cur INTO

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

4955: 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);
4956: END IF;
4957:
4958: IF l_contact_pt_contains_str IS NOT NULL THEN
4959: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4960: cnt := 1;
4961: LOOP
4962: FETCH l_contact_pt_cur INTO
4963: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 5030: l_party_contains_str VARCHAR2(32000);

5026: x_search_ctx_id IN OUT NUMBER
5027: ) IS
5028:
5029: -- Strings to hold the generated Intermedia query strings
5030: l_party_contains_str VARCHAR2(32000);
5031: l_party_site_contains_str VARCHAR2(32000);
5032: l_contact_contains_str VARCHAR2(32000);
5033: l_contact_pt_contains_str VARCHAR2(32000);
5034: l_tmp VARCHAR2(32000);

Line 5031: l_party_site_contains_str VARCHAR2(32000);

5027: ) IS
5028:
5029: -- Strings to hold the generated Intermedia query strings
5030: l_party_contains_str VARCHAR2(32000);
5031: l_party_site_contains_str VARCHAR2(32000);
5032: l_contact_contains_str VARCHAR2(32000);
5033: l_contact_pt_contains_str VARCHAR2(32000);
5034: l_tmp VARCHAR2(32000);
5035:

Line 5032: l_contact_contains_str VARCHAR2(32000);

5028:
5029: -- Strings to hold the generated Intermedia query strings
5030: l_party_contains_str VARCHAR2(32000);
5031: l_party_site_contains_str VARCHAR2(32000);
5032: l_contact_contains_str VARCHAR2(32000);
5033: l_contact_pt_contains_str VARCHAR2(32000);
5034: l_tmp VARCHAR2(32000);
5035:
5036: -- Other local variables

Line 5033: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

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

Line 5124: IF l_party_site_contains_str IS NOT NULL THEN

5120: 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);
5121: END LOOP;
5122: CLOSE l_party_cur;
5123:
5124: IF l_party_site_contains_str IS NOT NULL THEN
5125: l_max_score := 0;
5126: l_max_id := 0;
5127: l_max_idx := 0;
5128: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5125: l_max_score := 0;
5126: l_max_id := 0;
5127: l_max_idx := 0;
5128: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5129: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5130: ELSE
5131: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5132: END IF;
5133: LOOP

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

5127: l_max_idx := 0;
5128: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5129: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5130: ELSE
5131: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5132: END IF;
5133: LOOP
5134: FETCH l_party_site_cur INTO
5135: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX4, l_TX3;

Line 5152: IF l_contact_contains_str IS NOT NULL THEN

5148: 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);
5149: END IF;
5150: END IF;
5151:
5152: IF l_contact_contains_str IS NOT NULL THEN
5153: l_max_score := 0;
5154: l_max_id := 0;
5155: l_max_idx := 0;
5156: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5152: IF l_contact_contains_str IS NOT NULL THEN
5153: l_max_score := 0;
5154: l_max_id := 0;
5155: l_max_idx := 0;
5156: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5157: LOOP
5158: FETCH l_contact_cur INTO
5159: l_org_contact_id, l_ct_party_id ;
5160: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5174: IF l_contact_pt_contains_str IS NOT NULL THEN

5170: 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);
5171: END IF;
5172: END IF;
5173:
5174: IF l_contact_pt_contains_str IS NOT NULL THEN
5175: l_max_score := 0;
5176: l_max_id := 0;
5177: l_max_idx := 0;
5178: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5175: l_max_score := 0;
5176: l_max_id := 0;
5177: l_max_idx := 0;
5178: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5179: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5180: ELSE
5181: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5182: END IF;
5183: LOOP

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

5177: l_max_idx := 0;
5178: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5179: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5180: ELSE
5181: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5182: END IF;
5183: LOOP
5184: FETCH l_contact_pt_cur INTO
5185: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;