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_41 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 956: l_party_contains_str VARCHAR2(32000);

952: p_party_max_score NUMBER,
953: p_denorm_max_score NUMBER,
954: p_non_denorm_max_score NUMBER,
955: p_threshold NUMBER) RETURN VARCHAR2 IS
956: l_party_contains_str VARCHAR2(32000);
957: l_party_filter VARCHAR2(1) := null;
958: l_prim_temp VARCHAR2(4000);
959: l_denorm_str VARCHAR2(4000);
960: BEGIN

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

996:
997: IF l_denorm_str IS NOT NULL THEN
998: IF (p_non_denorm_max_score+50)>=p_threshold THEN
999: l_denorm_str := NULL;
1000: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1001: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1002: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1003: l_denorm_str := NULL;
1004: END IF;

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

998: IF (p_non_denorm_max_score+50)>=p_threshold THEN
999: l_denorm_str := NULL;
1000: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1001: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1002: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1003: l_denorm_str := NULL;
1004: END IF;
1005: ELSE
1006: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1002: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1003: l_denorm_str := NULL;
1004: END IF;
1005: ELSE
1006: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1007: END IF;
1008:
1009: -- Setup query string for TAX_REFERENCE
1010: l_prim_temp := null;

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

1013:
1014: IF l_denorm_str IS NOT NULL THEN
1015: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1016: l_denorm_str := NULL;
1017: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1018: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1019: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1020: l_denorm_str := NULL;
1021: END IF;

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

1015: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1016: l_denorm_str := NULL;
1017: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1018: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1019: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1020: l_denorm_str := NULL;
1021: END IF;
1022: ELSE
1023: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1019: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1020: l_denorm_str := NULL;
1021: END IF;
1022: ELSE
1023: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1024: END IF;
1025: IF lengthb(l_party_contains_str) > 4000 THEN
1026: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1027: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 1025: IF lengthb(l_party_contains_str) > 4000 THEN

1021: END IF;
1022: ELSE
1023: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1024: END IF;
1025: IF lengthb(l_party_contains_str) > 4000 THEN
1026: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1027: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1028: FND_MSG_PUB.ADD;
1029: RAISE FND_API.G_EXC_ERROR;

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

1027: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1028: FND_MSG_PUB.ADD;
1029: RAISE FND_API.G_EXC_ERROR;
1030: END IF;
1031: IF (p_match_str = ' AND ' OR p_party_max_score 1032: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1033: 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);
1034: END IF;
1035: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

1029: RAISE FND_API.G_EXC_ERROR;
1030: END IF;
1031: IF (p_match_str = ' AND ' OR p_party_max_score 1032: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1033: 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);
1034: END IF;
1035: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1036: 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);
1037: END IF;

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

1032: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1033: 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);
1034: END IF;
1035: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1036: 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);
1037: END IF;
1038: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1039: 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);
1040: END IF;

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

1037: END IF;
1038: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1039: 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);
1040: END IF;
1041: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1042: ELSE
1043: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1044: 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);
1045: 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 1044: 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);

1040: END IF;
1041: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1042: ELSE
1043: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1044: 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);
1045: 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);
1046: END IF;
1047: RETURN l_party_contains_str;
1048: END IF;

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

1041: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1042: ELSE
1043: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1044: 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);
1045: 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);
1046: END IF;
1047: RETURN l_party_contains_str;
1048: END IF;
1049: END;

Line 1047: RETURN l_party_contains_str;

1043: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1044: 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);
1045: 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);
1046: END IF;
1047: RETURN l_party_contains_str;
1048: END IF;
1049: END;
1050: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1051: l_contains_str VARCHAR2(32000);

Line 1051: l_contains_str VARCHAR2(32000);

1047: RETURN l_party_contains_str;
1048: END IF;
1049: END;
1050: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1051: l_contains_str VARCHAR2(32000);
1052: l_contains_str_temp VARCHAR2(32000);
1053: l_den_contains_str VARCHAR2(32000);
1054: l_den_contains_str_temp VARCHAR2(32000);
1055: l_filter_str VARCHAR2(4000) := null;

Line 1052: l_contains_str_temp VARCHAR2(32000);

1048: END IF;
1049: END;
1050: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1051: l_contains_str VARCHAR2(32000);
1052: l_contains_str_temp VARCHAR2(32000);
1053: l_den_contains_str VARCHAR2(32000);
1054: l_den_contains_str_temp VARCHAR2(32000);
1055: l_filter_str VARCHAR2(4000) := null;
1056: l_prim_temp VARCHAR2(4000) := null;

Line 1053: l_den_contains_str VARCHAR2(32000);

1049: END;
1050: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1051: l_contains_str VARCHAR2(32000);
1052: l_contains_str_temp VARCHAR2(32000);
1053: l_den_contains_str VARCHAR2(32000);
1054: l_den_contains_str_temp VARCHAR2(32000);
1055: l_filter_str VARCHAR2(4000) := null;
1056: l_prim_temp VARCHAR2(4000) := null;
1057: l_prim_temp_den VARCHAR2(4000) := null;

Line 1054: l_den_contains_str_temp VARCHAR2(32000);

1050: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1051: l_contains_str VARCHAR2(32000);
1052: l_contains_str_temp VARCHAR2(32000);
1053: l_den_contains_str VARCHAR2(32000);
1054: l_den_contains_str_temp VARCHAR2(32000);
1055: l_filter_str VARCHAR2(4000) := null;
1056: l_prim_temp VARCHAR2(4000) := null;
1057: l_prim_temp_den VARCHAR2(4000) := null;
1058: BEGIN

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

1060: 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);
1061: END IF;
1062: x_denorm_str := NULL;
1063:
1064: -- Dynamic setup of party site contains str
1065: --
1066: -- For each primary transformation add to intermedia query if it
1067: -- is not null
1068: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 1069: l_contains_str_temp := null;

1065: --
1066: -- For each primary transformation add to intermedia query if it
1067: -- is not null
1068: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1069: l_contains_str_temp := null;
1070: l_den_contains_str_temp := null;
1071: l_filter_str := null;
1072:
1073: -- Setup query string for ADDRESS

Line 1070: l_den_contains_str_temp := null;

1066: -- For each primary transformation add to intermedia query if it
1067: -- is not null
1068: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1069: l_contains_str_temp := null;
1070: l_den_contains_str_temp := null;
1071: l_filter_str := null;
1072:
1073: -- Setup query string for ADDRESS
1074: l_prim_temp := null;

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

1074: l_prim_temp := null;
1075: l_prim_temp_den := null;
1076: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1077: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1078: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1079:
1080: -- Setup query string for POSTAL_CODE
1081: l_prim_temp := null;
1082: l_prim_temp_den := null;

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

1083: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1084: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1085: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1086: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1087: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1088: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1089:
1090: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1091: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

1084: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1085: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1086: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1087: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1088: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1089:
1090: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1091: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1092: END LOOP;

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

1086: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1087: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1088: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1089:
1090: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1091: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1092: END LOOP;
1093: -- Add the search criteria to query string
1094: IF lengthb(l_contains_str) > 4000 THEN

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

1087: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1088: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1089:
1090: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1091: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1092: END LOOP;
1093: -- Add the search criteria to query string
1094: IF lengthb(l_contains_str) > 4000 THEN
1095: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1094: IF lengthb(l_contains_str) > 4000 THEN

1090: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1091: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1092: END LOOP;
1093: -- Add the search criteria to query string
1094: IF lengthb(l_contains_str) > 4000 THEN
1095: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1096: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1097: FND_MSG_PUB.ADD;
1098: RAISE FND_API.G_EXC_ERROR;

Line 1100: x_denorm_str := l_den_contains_str;

1096: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1097: FND_MSG_PUB.ADD;
1098: RAISE FND_API.G_EXC_ERROR;
1099: END IF;
1100: x_denorm_str := l_den_contains_str;
1101: RETURN l_contains_str;
1102: END;
1103:
1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1101: RETURN l_contains_str;

1097: FND_MSG_PUB.ADD;
1098: RAISE FND_API.G_EXC_ERROR;
1099: END IF;
1100: x_denorm_str := l_den_contains_str;
1101: RETURN l_contains_str;
1102: END;
1103:
1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1105: l_contains_str VARCHAR2(32000);

Line 1105: l_contains_str VARCHAR2(32000);

1101: RETURN l_contains_str;
1102: END;
1103:
1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1105: l_contains_str VARCHAR2(32000);
1106: l_contains_str_temp VARCHAR2(32000);
1107: l_den_contains_str VARCHAR2(32000);
1108: l_den_contains_str_temp VARCHAR2(32000);
1109: l_filter_str VARCHAR2(4000) := null;

Line 1106: l_contains_str_temp VARCHAR2(32000);

1102: END;
1103:
1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1105: l_contains_str VARCHAR2(32000);
1106: l_contains_str_temp VARCHAR2(32000);
1107: l_den_contains_str VARCHAR2(32000);
1108: l_den_contains_str_temp VARCHAR2(32000);
1109: l_filter_str VARCHAR2(4000) := null;
1110: l_prim_temp VARCHAR2(4000) := null;

Line 1107: l_den_contains_str VARCHAR2(32000);

1103:
1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1105: l_contains_str VARCHAR2(32000);
1106: l_contains_str_temp VARCHAR2(32000);
1107: l_den_contains_str VARCHAR2(32000);
1108: l_den_contains_str_temp VARCHAR2(32000);
1109: l_filter_str VARCHAR2(4000) := null;
1110: l_prim_temp VARCHAR2(4000) := null;
1111: l_prim_temp_den VARCHAR2(4000) := null;

Line 1108: l_den_contains_str_temp VARCHAR2(32000);

1104: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1105: l_contains_str VARCHAR2(32000);
1106: l_contains_str_temp VARCHAR2(32000);
1107: l_den_contains_str VARCHAR2(32000);
1108: l_den_contains_str_temp VARCHAR2(32000);
1109: l_filter_str VARCHAR2(4000) := null;
1110: l_prim_temp VARCHAR2(4000) := null;
1111: l_prim_temp_den VARCHAR2(4000) := null;
1112: BEGIN

Line 1121: l_contains_str VARCHAR2(32000);

1117: RETURN NULL;
1118: END;
1119:
1120: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1121: l_contains_str VARCHAR2(32000);
1122: l_contains_str_temp VARCHAR2(32000);
1123: l_den_contains_str VARCHAR2(32000);
1124: l_den_contains_str_temp VARCHAR2(32000);
1125: l_filter_str VARCHAR2(4000) := null;

Line 1122: l_contains_str_temp VARCHAR2(32000);

1118: END;
1119:
1120: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1121: l_contains_str VARCHAR2(32000);
1122: l_contains_str_temp VARCHAR2(32000);
1123: l_den_contains_str VARCHAR2(32000);
1124: l_den_contains_str_temp VARCHAR2(32000);
1125: l_filter_str VARCHAR2(4000) := null;
1126: l_prim_temp VARCHAR2(4000) := null;

Line 1123: l_den_contains_str VARCHAR2(32000);

1119:
1120: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1121: l_contains_str VARCHAR2(32000);
1122: l_contains_str_temp VARCHAR2(32000);
1123: l_den_contains_str VARCHAR2(32000);
1124: l_den_contains_str_temp VARCHAR2(32000);
1125: l_filter_str VARCHAR2(4000) := null;
1126: l_prim_temp VARCHAR2(4000) := null;
1127: l_prim_temp_den VARCHAR2(4000) := null;

Line 1124: l_den_contains_str_temp VARCHAR2(32000);

1120: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1121: l_contains_str VARCHAR2(32000);
1122: l_contains_str_temp VARCHAR2(32000);
1123: l_den_contains_str VARCHAR2(32000);
1124: l_den_contains_str_temp VARCHAR2(32000);
1125: l_filter_str VARCHAR2(4000) := null;
1126: l_prim_temp VARCHAR2(4000) := null;
1127: l_prim_temp_den VARCHAR2(4000) := null;
1128: TYPE CONTACT_PT_REC_TYPE IS RECORD (

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

1136: hz_utility_v2pub.debug(p_message=>'INIT_CONTACT_POINTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1137: END IF;
1138: x_denorm_str := NULL;
1139:
1140: -- Dynamic setup of party site contains str
1141: --
1142: -- For each primary transformation add to intermedia query if it
1143: -- is not null
1144: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP

Line 1145: l_contains_str_temp := null;

1141: --
1142: -- For each primary transformation add to intermedia query if it
1143: -- is not null
1144: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1145: l_contains_str_temp := null;
1146: l_den_contains_str_temp := null;
1147: l_filter_str := null;
1148: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1149: if(l_cnt_pt_type.count>0) then

Line 1146: l_den_contains_str_temp := null;

1142: -- For each primary transformation add to intermedia query if it
1143: -- is not null
1144: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1145: l_contains_str_temp := null;
1146: l_den_contains_str_temp := null;
1147: l_filter_str := null;
1148: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1149: if(l_cnt_pt_type.count>0) then
1150: x_modify := 'Y';

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

1166: l_prim_temp := null;
1167: l_prim_temp_den := null;
1168: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1169: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1170: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1171:
1172: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1173: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1174: IF N>1 THEN

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

1168: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1169: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1170: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1171:
1172: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1173: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1174: IF N>1 THEN
1175: distinct_search_cpt_types := N-1;
1176: ELSE

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

1169: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1170: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1171:
1172: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1173: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1174: IF N>1 THEN
1175: distinct_search_cpt_types := N-1;
1176: ELSE
1177: distinct_search_cpt_types := N;

Line 1184: IF lengthb(l_contains_str) > 4000 THEN

1180: hz_utility_v2pub.debug(p_message=>'distinct_search_cpt_types is - '||distinct_search_cpt_types,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1181: END IF;
1182: END LOOP;
1183: -- Add the search criteria to query string
1184: IF lengthb(l_contains_str) > 4000 THEN
1185: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1186: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1187: FND_MSG_PUB.ADD;
1188: RAISE FND_API.G_EXC_ERROR;

Line 1190: x_denorm_str := l_den_contains_str;

1186: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1187: FND_MSG_PUB.ADD;
1188: RAISE FND_API.G_EXC_ERROR;
1189: END IF;
1190: x_denorm_str := l_den_contains_str;
1191: RETURN l_contains_str;
1192: END;
1193:
1194: PROCEDURE open_party_cursor_direct (

Line 1191: RETURN l_contains_str;

1187: FND_MSG_PUB.ADD;
1188: RAISE FND_API.G_EXC_ERROR;
1189: END IF;
1190: x_denorm_str := l_den_contains_str;
1191: RETURN l_contains_str;
1192: END;
1193:
1194: PROCEDURE open_party_cursor_direct (
1195: p_dup_party_id NUMBER,

Line 1199: p_party_contains_str VARCHAR2,

1195: p_dup_party_id NUMBER,
1196: p_restrict_sql VARCHAR2,
1197: p_match_str VARCHAR2,
1198: p_search_merged VARCHAR2,
1199: p_party_contains_str VARCHAR2,
1200: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1201: l_sqlstr VARCHAR2(4000);
1202: l_search_merged VARCHAR2(1);
1203: BEGIN

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

1212: END IF;
1213: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1214: 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);
1215: END IF;
1216: 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
1217: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1218: 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);
1219: END IF;
1220: OPEN x_cursor FOR

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

1244: l_sqlstr := l_sqlstr || ' (TX44 like :TX44||'' %'' ';
1245: l_sqlstr := l_sqlstr || ' )) ';
1246: l_sqlstr := l_sqlstr || ' ) ';
1247: END IF;
1248: 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
1249: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1250: 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);
1251: END IF;
1252: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

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

1246: l_sqlstr := l_sqlstr || ' ) ';
1247: END IF;
1248: 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
1249: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1250: 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);
1251: END IF;
1252: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1253: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX11||'' %'' ';
1254: l_sqlstr := l_sqlstr || ' )) ';

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

1267: END IF;
1268: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1269: 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);
1270: END IF;
1271: 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
1272: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1273: 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);
1274: END IF;
1275: 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 1273: 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);

1269: 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);
1270: END IF;
1271: 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
1272: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1273: 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);
1274: END IF;
1275: 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
1276: ,g_party_stage_rec.TX44
1277: ,g_party_stage_rec.TX44

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

1285: 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
1286: ,g_party_stage_rec.TX44
1287: ,g_party_stage_rec.TX44
1288: ;
1289: 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
1290: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1291: 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);
1292: END IF;
1293: 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 1291: 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);

1287: ,g_party_stage_rec.TX44
1288: ;
1289: 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
1290: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1291: 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);
1292: END IF;
1293: 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
1294: ,g_party_site_stage_list(1).TX11
1295: ,g_party_site_stage_list(1).TX11

Line 1308: p_contains_str VARCHAR2,

1304: END;
1305: PROCEDURE open_party_cursor(
1306: p_dup_party_id NUMBER,
1307: p_restrict_sql VARCHAR2,
1308: p_contains_str VARCHAR2,
1309: p_search_ctx_id NUMBER,
1310: p_match_str VARCHAR2,
1311: p_search_merged VARCHAR2,
1312: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1327: IF p_contains_str IS NULL THEN

1323: l_search_merged := 'N';
1324: ELSE
1325: l_search_merged := p_search_merged;
1326: END IF;
1327: IF p_contains_str IS NULL THEN
1328: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1329: 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);
1330: END IF;
1331: OPEN x_cursor FOR

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

1325: l_search_merged := p_search_merged;
1326: END IF;
1327: IF p_contains_str IS NULL THEN
1328: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1329: 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);
1330: END IF;
1331: OPEN x_cursor FOR
1332: SELECT PARTY_ID , TX2, TX8, TX44
1333: FROM HZ_STAGED_PARTIES stage

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

1342: END IF;
1343: OPEN x_cursor FOR
1344: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX2, TX8, TX44
1345: FROM HZ_STAGED_PARTIES stage
1346: WHERE contains( concat_col, p_contains_str)>0
1347: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1348: AND( (l_search_merged ='Y' )
1349: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1350: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1355: END IF;
1356: OPEN x_cursor FOR
1357: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX2, TX8, TX44
1358: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1359: WHERE contains( concat_col, p_contains_str)>0
1360: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1361: AND d.party_id = stage.party_id
1362: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1363: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1380: END IF;
1381: IF p_search_ctx_id IS NULL THEN
1382: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX2, TX8, TX44'||
1383: ' FROM HZ_STAGED_PARTIES stage'||
1384: ' WHERE contains( concat_col, :cont)>0'||
1385: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1386: ' AND ('||p_restrict_sql||')' ||
1387: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1388: IF l_search_merged = 'Y' THEN

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

1392: ELSE
1393: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1394: END IF;
1395: output_long_strings('----------------------------------------------------------');
1396: output_long_strings('Party Contains String = '||p_contains_str);
1397: output_long_strings('Restrict Sql = '||p_restrict_sql);
1398: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1399: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1400: ,p_dup_party_id, p_dup_party_id;

Line 1398: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1394: END IF;
1395: output_long_strings('----------------------------------------------------------');
1396: output_long_strings('Party Contains String = '||p_contains_str);
1397: output_long_strings('Restrict Sql = '||p_restrict_sql);
1398: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1399: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1400: ,p_dup_party_id, p_dup_party_id;
1401: END IF;
1402: END IF;

Line 1429: p_contains_str VARCHAR2,

1425: PROCEDURE open_party_site_cursor(
1426: p_dup_party_id NUMBER,
1427: p_party_id NUMBER,
1428: p_restrict_sql VARCHAR2,
1429: p_contains_str VARCHAR2,
1430: p_search_ctx_id NUMBER,
1431: p_search_merged VARCHAR2,
1432: p_search_rel_sites VARCHAR2,
1433: p_person_api VARCHAR2,

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

1457: IF p_search_rel_sites = 'N' THEN
1458: OPEN x_cursor FOR
1459: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1460: FROM HZ_STAGED_PARTY_SITES stage
1461: WHERE contains( concat_col, p_contains_str)>0
1462: AND( (l_search_merged ='Y' )
1463: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1464: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1465: AND stage.party_id = p_party_id;

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

1466: ELSE
1467: OPEN x_cursor FOR
1468: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1469: FROM HZ_STAGED_PARTY_SITES stage
1470: WHERE contains( concat_col, p_contains_str)>0
1471: AND( (l_search_merged ='Y' )
1472: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1473: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1474: AND stage.party_id = p_party_id

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

1474: AND stage.party_id = p_party_id
1475: UNION
1476: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX11
1477: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1478: WHERE contains( concat_col, p_contains_str)>0
1479: AND( (l_search_merged ='Y' )
1480: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1481: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1482: AND r.object_id = p_party_id

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

1496: END IF;
1497: OPEN x_cursor FOR
1498: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1499: FROM HZ_STAGED_PARTY_SITES stage
1500: WHERE contains( concat_col, p_contains_str)>0
1501: AND EXISTS (
1502: SELECT 1 FROM HZ_STAGED_PARTIES p
1503: WHERE p.PARTY_ID = stage.PARTY_ID
1504: AND( (l_search_merged ='Y' )

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

1516: IF p_person_api = 'Y' THEN
1517: OPEN x_cursor FOR
1518: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1519: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1520: WHERE contains( concat_col, p_contains_str)>0
1521: AND( (l_search_merged ='Y' )
1522: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1523: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1524: AND d.search_context_id = p_search_ctx_id

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

1526: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1527: UNION
1528: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX11
1529: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1530: WHERE contains( concat_col, p_contains_str)>0
1531: AND( (l_search_merged ='Y' )
1532: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1533: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1534: AND d.search_context_id = p_search_ctx_id

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

1539: ELSE
1540: OPEN x_cursor FOR
1541: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1542: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1543: WHERE contains( concat_col, p_contains_str)>0
1544: AND( (l_search_merged ='Y' )
1545: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1546: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1547: AND d.search_context_id = p_search_ctx_id

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

1584: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1585: END IF;
1586: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1587: ' FROM HZ_STAGED_PARTY_SITES stage'||
1588: ' WHERE contains( concat_col, :cont)>0'||
1589: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1590: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1591: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1592: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1594: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1595: ' UNION ' ||
1596: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1597: ' FROM HZ_STAGED_PARTY_SITES stage'||
1598: ' WHERE contains( concat_col, :cont)>0'||
1599: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1600: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1601: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1602: ' AND ORG_CONTACT_ID IN ' ||

Line 1608: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1604: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1605: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1606: ' and ('||p_restrict_sql1|| ') )' ||
1607: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1608: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1609: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1610: ELSE
1611: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1612: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1609: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1605: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1606: ' and ('||p_restrict_sql1|| ') )' ||
1607: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1608: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1609: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1610: ELSE
1611: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1612: ' FROM HZ_STAGED_PARTY_SITES stage'||
1613: ' WHERE contains( concat_col, :cont)>0'||

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

1609: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1610: ELSE
1611: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1612: ' FROM HZ_STAGED_PARTY_SITES stage'||
1613: ' WHERE contains( concat_col, :cont)>0'||
1614: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1615: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1616: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1617: ' AND EXISTS ('||

Line 1623: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1619: ' WHERE p.party_id = stage.party_id ' ||
1620: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1621: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1622: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1623: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1624: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1625: ,p_dup_party_id, p_dup_party_id;
1626: END IF;
1627: ELSE

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

1626: END IF;
1627: ELSE
1628: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1629: ' FROM HZ_STAGED_PARTY_SITES stage'||
1630: ' WHERE contains( concat_col, :cont)>0'||
1631: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1632: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1633: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1634: ' AND EXISTS ('||

Line 1640: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1636: ' WHERE p.party_id = stage.party_id ' ||
1637: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1638: ' AND ('||p_restrict_sql||')' ||
1639: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1640: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1641: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1642: ,p_dup_party_id, p_dup_party_id;
1643: END IF;
1644: END IF;

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

1642: ,p_dup_party_id, p_dup_party_id;
1643: END IF;
1644: END IF;
1645: output_long_strings('----------------------------------------------------------');
1646: output_long_strings('Party Site Contains String = '||p_contains_str);
1647: output_long_strings('Restrict Sql = '||p_restrict_sql);
1648: exception
1649: when others then
1650: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1674: p_contains_str VARCHAR2,

1670: PROCEDURE open_contact_cursor(
1671: p_dup_party_id NUMBER,
1672: p_party_id NUMBER,
1673: p_restrict_sql VARCHAR2,
1674: p_contains_str VARCHAR2,
1675: p_search_ctx_id NUMBER,
1676: p_search_merged VARCHAR2,
1677: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1678: l_sqlstr VARCHAR2(4000);

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

1698: END IF;
1699: OPEN x_cursor FOR
1700: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1701: FROM HZ_STAGED_CONTACTS stage
1702: WHERE contains( concat_col, p_contains_str)>0
1703: AND EXISTS (
1704: SELECT 1 FROM HZ_STAGED_PARTIES p
1705: WHERE p.PARTY_ID = stage.PARTY_ID
1706: AND( (l_search_merged ='Y' )

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

1721: END IF;
1722: OPEN x_cursor FOR
1723: SELECT ORG_CONTACT_ID, PARTY_ID
1724: FROM HZ_STAGED_CONTACTS stage
1725: WHERE contains( concat_col, p_contains_str)>0
1726: AND EXISTS (
1727: SELECT 1 FROM HZ_STAGED_PARTIES p
1728: WHERE p.PARTY_ID = stage.PARTY_ID
1729: AND( (l_search_merged ='Y' )

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

1740: END IF;
1741: OPEN x_cursor FOR
1742: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1743: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1744: WHERE contains( concat_col, p_contains_str)>0
1745: AND d.search_context_id = p_search_ctx_id
1746: AND d.party_id = stage.party_id
1747: AND( (l_search_merged ='Y' )
1748: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1774: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1775: END IF;
1776: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1777: ' FROM HZ_STAGED_CONTACTS stage'||
1778: ' WHERE contains( concat_col, :cont)>0'||
1779: ' AND EXISTS ('||
1780: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1781: ' WHERE p.party_id = stage.party_id ' ||
1782: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1788: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1784: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1785: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1786: ' AND ('||p_restrict_sql||')' ||
1787: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1788: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1789: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1790: ,p_dup_party_id, p_dup_party_id;
1791: END IF;
1792: output_long_strings('----------------------------------------------------------');

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

1789: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1790: ,p_dup_party_id, p_dup_party_id;
1791: END IF;
1792: output_long_strings('----------------------------------------------------------');
1793: output_long_strings('Contacts Contains String = '||p_contains_str);
1794: output_long_strings('Restrict Sql = '||p_restrict_sql);
1795: exception
1796: when others then
1797: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1821: p_contains_str VARCHAR2,

1817: PROCEDURE open_contact_pt_cursor(
1818: p_dup_party_id NUMBER,
1819: p_party_id NUMBER,
1820: p_restrict_sql VARCHAR2,
1821: p_contains_str VARCHAR2,
1822: p_search_ctx_id NUMBER,
1823: p_search_merged VARCHAR2,
1824: p_search_rel_cpts VARCHAR2,
1825: p_person_api VARCHAR2,

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

1847: THEN
1848: OPEN x_cursor FOR
1849: 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 , TX1, TX6, TX5
1850: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1851: WHERE contains( concat_col, p_contains_str)>0
1852: AND d.search_context_id = p_search_ctx_id
1853: AND( (l_search_merged ='Y' )
1854: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1855: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1859: THEN
1860: OPEN x_cursor FOR
1861: 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 , TX1, TX6, TX5
1862: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1863: WHERE contains( concat_col, p_contains_str)>0
1864: AND d.search_context_id = p_search_ctx_id
1865: AND( (l_search_merged ='Y' )
1866: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1867: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1876: IF p_search_rel_cpts = 'N' THEN
1877: OPEN x_cursor FOR
1878: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1879: FROM HZ_STAGED_CONTACT_POINTS stage
1880: WHERE contains( concat_col, p_contains_str)>0
1881: AND( (l_search_merged ='Y' )
1882: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1883: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1884: AND stage.party_id = p_party_id;

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

1885: ELSE
1886: OPEN x_cursor FOR
1887: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1888: FROM HZ_STAGED_CONTACT_POINTS stage
1889: WHERE contains( concat_col, p_contains_str)>0
1890: AND( (l_search_merged ='Y' )
1891: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1892: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1893: AND stage.party_id = p_party_id

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

1893: AND stage.party_id = p_party_id
1894: UNION
1895: 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 , TX1, TX6, TX5
1896: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1897: WHERE contains( concat_col, p_contains_str)>0
1898: AND r.object_id = p_party_id
1899: AND( (l_search_merged ='Y' )
1900: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1901: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1915: END IF;
1916: OPEN x_cursor FOR
1917: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1918: FROM HZ_STAGED_CONTACT_POINTS stage
1919: WHERE contains( concat_col, p_contains_str)>0
1920: AND EXISTS (
1921: SELECT 1 FROM HZ_STAGED_PARTIES p
1922: WHERE p.PARTY_ID = stage.PARTY_ID
1923: AND( (l_search_merged ='Y' )

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

1935: IF p_person_api = 'Y' THEN
1936: OPEN x_cursor FOR
1937: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1938: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1939: WHERE contains( concat_col, p_contains_str)>0
1940: AND d.search_context_id = p_search_ctx_id
1941: AND d.party_id = stage.party_id
1942: AND( (l_search_merged ='Y' )
1943: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1945: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1946: UNION
1947: 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 , TX1, TX6, TX5
1948: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1949: WHERE contains( concat_col, p_contains_str)>0
1950: AND d.search_context_id = p_search_ctx_id
1951: AND d.party_id = r.subject_id
1952: AND r.relationship_id = oc.party_relationship_id
1953: AND oc.org_contact_id = stage.org_contact_id

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

1958: ELSE
1959: OPEN x_cursor FOR
1960: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1961: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1962: WHERE contains( concat_col, p_contains_str)>0
1963: AND d.search_context_id = p_search_ctx_id
1964: AND d.party_id = stage.party_id
1965: AND( (l_search_merged ='Y' )
1966: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2003: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2004: END IF;
2005: 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 , TX1, TX6, TX5'||
2006: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2007: ' WHERE contains( concat_col, :cont)>0 '||
2008: ' AND (stage.org_contact_id is null '||
2009: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2010: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2011: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

2013: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2014: ' UNION ' ||
2015: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2016: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2017: ' WHERE contains( concat_col, :cont)>0 '||
2018: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2019: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2020: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2021: ' AND (stage.org_contact_id in ' ||

Line 2027: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

2023: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2024: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2025: ' and ('||p_restrict_sql1 || ') ) )' ||
2026: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2027: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2028: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2029: ELSE
2030: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2031: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 2028: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

2024: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2025: ' and ('||p_restrict_sql1 || ') ) )' ||
2026: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2027: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2028: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2029: ELSE
2030: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2031: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2032: ' WHERE contains( concat_col, :cont)>0'||

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

2028: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2029: ELSE
2030: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2031: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2032: ' WHERE contains( concat_col, :cont)>0'||
2033: ' AND EXISTS ('||
2034: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2035: ' WHERE p.party_id = stage.party_id ' ||
2036: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2042: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2038: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2039: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2040: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2041: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2042: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2043: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2044: ,p_dup_party_id, p_dup_party_id;
2045: END IF;
2046: ELSE

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

2045: END IF;
2046: ELSE
2047: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2048: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2049: ' WHERE contains( concat_col, :cont)>0'||
2050: ' AND EXISTS ('||
2051: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2052: ' WHERE p.party_id = stage.party_id ' ||
2053: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2059: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2055: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2056: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2057: ' AND ('||p_restrict_sql||')' ||
2058: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2059: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2060: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2061: ,p_dup_party_id, p_dup_party_id;
2062: END IF;
2063: END IF;

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

2062: END IF;
2063: END IF;
2064: END IF;
2065: output_long_strings('----------------------------------------------------------');
2066: output_long_strings('Contact Points Contains String = '||p_contains_str);
2067: output_long_strings('Restrict Sql = '||p_restrict_sql);
2068: exception
2069: when others then
2070: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2187: l_party_contains_str VARCHAR2(32000);

2183: x_num_matches OUT NUMBER
2184: ) IS
2185:
2186: -- Strings to hold the generated Intermedia query strings
2187: l_party_contains_str VARCHAR2(32000);
2188: l_party_site_contains_str VARCHAR2(32000);
2189: l_contact_contains_str VARCHAR2(32000);
2190: l_contact_pt_contains_str VARCHAR2(32000);
2191: l_denorm_str VARCHAR2(32000);

Line 2188: l_party_site_contains_str VARCHAR2(32000);

2184: ) IS
2185:
2186: -- Strings to hold the generated Intermedia query strings
2187: l_party_contains_str VARCHAR2(32000);
2188: l_party_site_contains_str VARCHAR2(32000);
2189: l_contact_contains_str VARCHAR2(32000);
2190: l_contact_pt_contains_str VARCHAR2(32000);
2191: l_denorm_str VARCHAR2(32000);
2192: l_ps_denorm_str VARCHAR2(32000);

Line 2189: l_contact_contains_str VARCHAR2(32000);

2185:
2186: -- Strings to hold the generated Intermedia query strings
2187: l_party_contains_str VARCHAR2(32000);
2188: l_party_site_contains_str VARCHAR2(32000);
2189: l_contact_contains_str VARCHAR2(32000);
2190: l_contact_pt_contains_str VARCHAR2(32000);
2191: l_denorm_str VARCHAR2(32000);
2192: l_ps_denorm_str VARCHAR2(32000);
2193: l_ct_denorm_str VARCHAR2(32000);

Line 2190: l_contact_pt_contains_str VARCHAR2(32000);

2186: -- Strings to hold the generated Intermedia query strings
2187: l_party_contains_str VARCHAR2(32000);
2188: l_party_site_contains_str VARCHAR2(32000);
2189: l_contact_contains_str VARCHAR2(32000);
2190: l_contact_pt_contains_str VARCHAR2(32000);
2191: l_denorm_str VARCHAR2(32000);
2192: l_ps_denorm_str VARCHAR2(32000);
2193: l_ct_denorm_str VARCHAR2(32000);
2194: l_cpt_denorm_str VARCHAR2(32000);

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

2280: call_type(2) := 'OR';
2281: END;
2282:
2283: /** Private procedure to acquire and score at party level ***/
2284: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2285: l_party_id_idx NUMBER:=1;
2286: l_ctx_id NUMBER;
2287: l_precalc_score BOOLEAN := FALSE;
2288: l_TX35_new varchar2(4000);

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

2312: l_search_ctx_id,H_PARTY_ID(I));
2313: l_party_id_idx := H_PARTY_ID.COUNT+1;
2314: ELSE
2315: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2316: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2317: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2318: 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);
2319: END IF;
2320: 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 2324: IF (l_party_site_contains_str IS NULL AND

2320: 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
2321: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2322: 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);
2323: END IF;
2324: IF (l_party_site_contains_str IS NULL AND
2325: l_contact_contains_str IS NULL AND
2326: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2327: 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);
2328: RAISE L_RETURN_IMM_EXC;

Line 2325: l_contact_contains_str IS NULL AND

2321: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2322: 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);
2323: END IF;
2324: IF (l_party_site_contains_str IS NULL AND
2325: l_contact_contains_str IS NULL AND
2326: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2327: 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);
2328: RAISE L_RETURN_IMM_EXC;
2329: ELSE

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

2322: 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);
2323: END IF;
2324: IF (l_party_site_contains_str IS NULL AND
2325: l_contact_contains_str IS NULL AND
2326: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2327: 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);
2328: RAISE L_RETURN_IMM_EXC;
2329: ELSE
2330: 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 2330: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2326: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2327: 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);
2328: RAISE L_RETURN_IMM_EXC;
2329: ELSE
2330: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2331: END IF;
2332: ELSE
2333: IF p_index>1 THEN
2334: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2349: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2350: 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);
2351: END IF;
2352: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2353: 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);
2354: END IF;
2355: LOOP
2356: FETCH l_party_cur INTO
2357: l_party_id , l_TX2, l_TX8, l_TX44;

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

2371: l_party_id_idx:= l_party_id_idx+1;
2372: END IF;
2373: IF (l_party_id_idx-1)>l_max_thresh THEN
2374: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2375: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2376: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2377: AND l_contact_pt_contains_str IS NULL THEN
2378: EXIT;
2379: END IF;

Line 2377: AND l_contact_pt_contains_str IS NULL THEN

2373: IF (l_party_id_idx-1)>l_max_thresh THEN
2374: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2375: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2376: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2377: AND l_contact_pt_contains_str IS NULL THEN
2378: EXIT;
2379: END IF;
2380: CLOSE l_party_cur;
2381: IF p_index>1 THEN

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

2402: ROLLBACK to eval_start;
2403: END;
2404:
2405: /** Private procedure to acquire and score at party site level ***/
2406: 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
2407: l_party_id_idx NUMBER:=1;
2408: l_ctx_id NUMBER;
2409: h_ps_id HZ_PARTY_SEARCH.IDList;
2410: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2414: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2415: 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);
2416: 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);
2417: END IF;
2418: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2419: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2420: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2421: l_party_id := H_SCORES.FIRST;
2422: WHILE l_party_id IS NOT NULL LOOP

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

2415: 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);
2416: 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);
2417: END IF;
2418: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2419: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2420: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2421: l_party_id := H_SCORES.FIRST;
2422: WHILE l_party_id IS NOT NULL LOOP
2423: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2416: 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);
2417: END IF;
2418: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2419: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2420: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2421: l_party_id := H_SCORES.FIRST;
2422: WHILE l_party_id IS NOT NULL LOOP
2423: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2424: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2446: l_search_ctx_id,H_PARTY_ID(I));
2447: l_party_id_idx := H_PARTY_ID.COUNT+1;
2448: ELSE
2449: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2450: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2451: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2452: 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);
2453: END IF;
2454: IF p_index>1 THEN

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

2468: END IF;
2469: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2470: 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);
2471: END IF;
2472: 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);
2473: LOOP
2474: FETCH l_party_site_cur INTO
2475: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
2476: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2524: END IF;
2525: END;
2526:
2527: /** Private procedure to acquire and score at contact point level ***/
2528: 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
2529: l_party_id_idx NUMBER:=1;
2530: l_ctx_id NUMBER;
2531: h_ct_id HZ_PARTY_SEARCH.IDList;
2532: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2558: l_search_ctx_id,H_PARTY_ID(I));
2559: l_party_id_idx := H_PARTY_ID.COUNT+1;
2560: ELSE
2561: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2562: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2563: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2564: 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);
2565: END IF;
2566: IF p_index>1 THEN

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

2580: END IF;
2581: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2582: 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);
2583: END IF;
2584: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2585: LOOP
2586: FETCH l_contact_cur INTO
2587: l_org_contact_id, l_ct_party_id ;
2588: EXIT WHEN l_contact_cur%NOTFOUND;

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

2633: END IF;
2634: END;
2635:
2636: /** Private procedure to acquire and score at contact point level ***/
2637: 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
2638: l_party_id_idx NUMBER:=1;
2639: l_ctx_id NUMBER;
2640: h_cpt_id HZ_PARTY_SEARCH.IDList;
2641: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2669: l_search_ctx_id,H_PARTY_ID(I));
2670: l_party_id_idx := H_PARTY_ID.COUNT+1;
2671: ELSE
2672: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2673: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2674: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2675: 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);
2676: END IF;
2677: IF p_index>1 THEN

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

2691: END IF;
2692: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2693: 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);
2694: END IF;
2695: 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);
2696: LOOP
2697: FETCH l_contact_pt_cur INTO
2698: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
2699: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2876: IF l_party_contains_str IS NOT NULL THEN

2872: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2873: l_threshold := round(( l_entered_max_score / 360) * 101);
2874: other_acq_criteria_exists := TRUE ;
2875: --check if acquisition criteria exists for any other entity
2876: IF l_party_contains_str IS NOT NULL THEN
2877: acq_cnt := 1;
2878: END IF;
2879: IF l_party_site_contains_str IS NOT NULL THEN
2880: acq_cnt := acq_cnt+1;

Line 2879: IF l_party_site_contains_str IS NOT NULL THEN

2875: --check if acquisition criteria exists for any other entity
2876: IF l_party_contains_str IS NOT NULL THEN
2877: acq_cnt := 1;
2878: END IF;
2879: IF l_party_site_contains_str IS NOT NULL THEN
2880: acq_cnt := acq_cnt+1;
2881: END IF;
2882: IF l_contact_contains_str IS NOT NULL THEN
2883: acq_cnt := acq_cnt+1;

Line 2882: IF l_contact_contains_str IS NOT NULL THEN

2878: END IF;
2879: IF l_party_site_contains_str IS NOT NULL THEN
2880: acq_cnt := acq_cnt+1;
2881: END IF;
2882: IF l_contact_contains_str IS NOT NULL THEN
2883: acq_cnt := acq_cnt+1;
2884: END IF;
2885: IF l_contact_pt_contains_str IS NOT NULL THEN
2886: acq_cnt := acq_cnt+1;

Line 2885: IF l_contact_pt_contains_str IS NOT NULL THEN

2881: END IF;
2882: IF l_contact_contains_str IS NOT NULL THEN
2883: acq_cnt := acq_cnt+1;
2884: END IF;
2885: IF l_contact_pt_contains_str IS NOT NULL THEN
2886: acq_cnt := acq_cnt+1;
2887: END IF;
2888: IF acq_cnt>1 THEN
2889: other_acq_criteria_exists := TRUE;

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

2915: FND_MSG_PUB.ADD;
2916: RAISE FND_API.G_EXC_ERROR;
2917: END IF;
2918: END IF;
2919: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2920: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2921: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2922: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2923: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

2916: RAISE FND_API.G_EXC_ERROR;
2917: END IF;
2918: END IF;
2919: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2920: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2921: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2922: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2923: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2924: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2917: END IF;
2918: END IF;
2919: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2920: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2921: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2922: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2923: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2924: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2925: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

2918: END IF;
2919: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2920: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2921: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2922: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2923: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2924: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2925: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2926: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2919: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2920: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2921: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2922: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2923: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2924: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2925: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2926: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2927: END IF;

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

2920: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2921: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2922: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2923: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2924: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2925: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2926: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2927: END IF;
2928: END;

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

2921: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2922: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2923: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2924: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2925: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2926: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2927: END IF;
2928: END;
2929: /************ End of find_parties private procedures **********/

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

2922: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2923: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2924: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2925: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2926: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2927: END IF;
2928: END;
2929: /************ End of find_parties private procedures **********/
2930:

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

2994: 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);
2995: END IF;
2996: -- ************************************************************
2997: -- STEP 2. Setup of intermedia query strings for Acquisition query
2998: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2999: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3000: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3001: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3002: 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 2999: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

2995: END IF;
2996: -- ************************************************************
2997: -- STEP 2. Setup of intermedia query strings for Acquisition query
2998: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2999: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3000: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3001: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3002: 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);
3003: END IF;

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

2996: -- ************************************************************
2997: -- STEP 2. Setup of intermedia query strings for Acquisition query
2998: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2999: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3000: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3001: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3002: 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);
3003: END IF;
3004: l_denorm_max_score:=0;

Line 3032: 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((101/360)*l_entered_max_score));

3028: END IF;
3029: ELSE
3030: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3031: END IF;
3032: 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((101/360)*l_entered_max_score));
3033: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3034:
3035: -- Setup Search Context ID
3036: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3042: IF l_party_contains_str IS NULL THEN

3038:
3039: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3040: 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);
3041: END IF;
3042: IF l_party_contains_str IS NULL THEN
3043: defpt := 1;
3044: END IF;
3045: IF l_party_site_contains_str IS NULL THEN
3046: defps := 1;

Line 3045: IF l_party_site_contains_str IS NULL THEN

3041: END IF;
3042: IF l_party_contains_str IS NULL THEN
3043: defpt := 1;
3044: END IF;
3045: IF l_party_site_contains_str IS NULL THEN
3046: defps := 1;
3047: END IF;
3048: IF l_contact_contains_str IS NULL THEN
3049: defct := 1;

Line 3048: IF l_contact_contains_str IS NULL THEN

3044: END IF;
3045: IF l_party_site_contains_str IS NULL THEN
3046: defps := 1;
3047: END IF;
3048: IF l_contact_contains_str IS NULL THEN
3049: defct := 1;
3050: END IF;
3051: IF l_contact_pt_contains_str IS NULL THEN
3052: defcpt := 1;

Line 3051: IF l_contact_pt_contains_str IS NULL THEN

3047: END IF;
3048: IF l_contact_contains_str IS NULL THEN
3049: defct := 1;
3050: END IF;
3051: IF l_contact_pt_contains_str IS NULL THEN
3052: defcpt := 1;
3053: END IF;
3054:
3055: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3056: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3057: 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);
3058: END IF;
3059: 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);
3060: 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);
3061: 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);
3062: 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);
3063: 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);
3064: 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 3061: 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);

3057: 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);
3058: END IF;
3059: 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);
3060: 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);
3061: 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);
3062: 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);
3063: 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);
3064: 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);
3065: END IF;

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

3058: END IF;
3059: 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);
3060: 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);
3061: 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);
3062: 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);
3063: 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);
3064: 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);
3065: END IF;
3066: FOR I in 1..5 LOOP

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

3059: 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);
3060: 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);
3061: 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);
3062: 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);
3063: 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);
3064: 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);
3065: END IF;
3066: FOR I in 1..5 LOOP
3067: do_eval(I);

Line 3145: l_party_contains_str VARCHAR2(32000);

3141: x_num_matches OUT NUMBER
3142: ) IS
3143:
3144: -- Strings to hold the generated Intermedia query strings
3145: l_party_contains_str VARCHAR2(32000);
3146: l_party_site_contains_str VARCHAR2(32000);
3147: l_contact_contains_str VARCHAR2(32000);
3148: l_contact_pt_contains_str VARCHAR2(32000);
3149: l_denorm_str VARCHAR2(32000);

Line 3146: l_party_site_contains_str VARCHAR2(32000);

3142: ) IS
3143:
3144: -- Strings to hold the generated Intermedia query strings
3145: l_party_contains_str VARCHAR2(32000);
3146: l_party_site_contains_str VARCHAR2(32000);
3147: l_contact_contains_str VARCHAR2(32000);
3148: l_contact_pt_contains_str VARCHAR2(32000);
3149: l_denorm_str VARCHAR2(32000);
3150: l_ps_denorm_str VARCHAR2(32000);

Line 3147: l_contact_contains_str VARCHAR2(32000);

3143:
3144: -- Strings to hold the generated Intermedia query strings
3145: l_party_contains_str VARCHAR2(32000);
3146: l_party_site_contains_str VARCHAR2(32000);
3147: l_contact_contains_str VARCHAR2(32000);
3148: l_contact_pt_contains_str VARCHAR2(32000);
3149: l_denorm_str VARCHAR2(32000);
3150: l_ps_denorm_str VARCHAR2(32000);
3151: l_ct_denorm_str VARCHAR2(32000);

Line 3148: l_contact_pt_contains_str VARCHAR2(32000);

3144: -- Strings to hold the generated Intermedia query strings
3145: l_party_contains_str VARCHAR2(32000);
3146: l_party_site_contains_str VARCHAR2(32000);
3147: l_contact_contains_str VARCHAR2(32000);
3148: l_contact_pt_contains_str VARCHAR2(32000);
3149: l_denorm_str VARCHAR2(32000);
3150: l_ps_denorm_str VARCHAR2(32000);
3151: l_ct_denorm_str VARCHAR2(32000);
3152: l_cpt_denorm_str VARCHAR2(32000);

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

3268: call_type(2) := 'OR';
3269: END;
3270:
3271: /** Private procedure to acquire and score at party level ***/
3272: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3273: l_party_id_idx NUMBER:=1;
3274: l_ctx_id NUMBER;
3275: l_TX35_new varchar2(4000);
3276: BEGIN

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

3290: l_search_ctx_id,H_PARTY_ID(I));
3291: l_party_id_idx := H_PARTY_ID.COUNT+1;
3292: ELSE
3293: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3294: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3295: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3296: 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);
3297: END IF;
3298: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3302: IF (l_party_site_contains_str IS NULL AND

3298: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3299: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3300: 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);
3301: END IF;
3302: IF (l_party_site_contains_str IS NULL AND
3303: l_contact_contains_str IS NULL AND
3304: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3305: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3306: RAISE L_RETURN_IMM_EXC;

Line 3303: l_contact_contains_str IS NULL AND

3299: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3300: 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);
3301: END IF;
3302: IF (l_party_site_contains_str IS NULL AND
3303: l_contact_contains_str IS NULL AND
3304: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3305: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3306: RAISE L_RETURN_IMM_EXC;
3307: ELSE

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

3300: 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);
3301: END IF;
3302: IF (l_party_site_contains_str IS NULL AND
3303: l_contact_contains_str IS NULL AND
3304: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3305: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3306: RAISE L_RETURN_IMM_EXC;
3307: ELSE
3308: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3304: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3305: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3306: RAISE L_RETURN_IMM_EXC;
3307: ELSE
3308: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3309: END IF;
3310: ELSE
3311: IF p_index>1 THEN
3312: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3324: l_ctx_id := NULL;
3325: l_party_id_idx := H_PARTY_ID.COUNT+1;
3326: END IF;
3327: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3328: 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);
3329: END IF;
3330: LOOP
3331: FETCH l_party_cur INTO
3332: l_party_id , l_TX2, l_TX8, l_TX44;

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

3346: l_party_id_idx:= l_party_id_idx+1;
3347: END IF;
3348: IF (l_party_id_idx-1)>l_max_thresh THEN
3349: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3350: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3351: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3352: AND l_contact_pt_contains_str IS NULL THEN
3353: H_PARTY_ID.DELETE(l_party_id_idx-1);
3354: H_PARTY_ID_LIST.DELETE(l_index);

Line 3352: AND l_contact_pt_contains_str IS NULL THEN

3348: IF (l_party_id_idx-1)>l_max_thresh THEN
3349: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3350: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3351: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3352: AND l_contact_pt_contains_str IS NULL THEN
3353: H_PARTY_ID.DELETE(l_party_id_idx-1);
3354: H_PARTY_ID_LIST.DELETE(l_index);
3355: H_SCORES.DELETE(l_index);
3356: EXIT;

Line 3381: p_contains_str VARCHAR2,

3377: CLOSE l_party_cur;
3378: ROLLBACK to eval_start;
3379: END;
3380: PROCEDURE open_person_contact_cursor(
3381: p_contains_str VARCHAR2,
3382: p_search_ctx_id NUMBER,
3383: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3384: BEGIN
3385: OPEN x_cursor FOR

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

3384: BEGIN
3385: OPEN x_cursor FOR
3386: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3387: FROM HZ_STAGED_CONTACTS stage
3388: WHERE contains( concat_col, p_contains_str)>0
3389: AND ORG_CONTACT_ID in (
3390: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3391: org_contact_id
3392: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3408: end if;
3409: END;
3410:
3411: /** Private procedure to acquire and score at party site level ***/
3412: 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
3413: l_party_id_idx NUMBER:=1;
3414: l_ctx_id NUMBER;
3415: h_ps_id HZ_PARTY_SEARCH.IDList;
3416: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3436: ELSE
3437: l_party_id_idx := H_PARTY_ID.COUNT+1;
3438: l_ctx_id := NULL;
3439: END IF;
3440: 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);
3441: LOOP
3442: FETCH l_party_site_cur INTO
3443: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
3444: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3507: END IF;
3508: END;
3509:
3510: /** Private procedure to acquire and score at party site level ***/
3511: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3512: l_party_id_idx NUMBER:=1;
3513: l_ctx_id NUMBER;
3514: h_ct_id HZ_PARTY_SEARCH.IDList;
3515: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3523: FORALL I in 1..H_PARTY_ID.COUNT
3524: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3525: l_search_ctx_id,H_PARTY_ID(I));
3526: l_party_id_idx := H_PARTY_ID.COUNT+1;
3527: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3528: LOOP
3529: FETCH l_contact_cur INTO
3530: l_org_contact_id, l_ct_party_id ;
3531: EXIT WHEN l_contact_cur%NOTFOUND;

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

3565: END IF;
3566: END;
3567:
3568: /** Private procedure to acquire and score at contact point level ***/
3569: 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
3570: l_party_id_idx NUMBER:=1;
3571: l_ctx_id NUMBER;
3572: h_cpt_id HZ_PARTY_SEARCH.IDList;
3573: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3610: END IF;
3611: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3612: 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);
3613: END IF;
3614: 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);
3615: LOOP
3616: FETCH l_contact_pt_cur INTO
3617: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
3618: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3817: IF l_party_contains_str IS NOT NULL THEN

3813: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3814: l_threshold := round(( l_entered_max_score / 360) * 101);
3815: other_acq_criteria_exists := TRUE ;
3816: --check if acquisition criteria exists for any other entity
3817: IF l_party_contains_str IS NOT NULL THEN
3818: acq_cnt := 1;
3819: END IF;
3820: IF l_party_site_contains_str IS NOT NULL THEN
3821: acq_cnt := acq_cnt+1;

Line 3820: IF l_party_site_contains_str IS NOT NULL THEN

3816: --check if acquisition criteria exists for any other entity
3817: IF l_party_contains_str IS NOT NULL THEN
3818: acq_cnt := 1;
3819: END IF;
3820: IF l_party_site_contains_str IS NOT NULL THEN
3821: acq_cnt := acq_cnt+1;
3822: END IF;
3823: IF l_contact_contains_str IS NOT NULL THEN
3824: acq_cnt := acq_cnt+1;

Line 3823: IF l_contact_contains_str IS NOT NULL THEN

3819: END IF;
3820: IF l_party_site_contains_str IS NOT NULL THEN
3821: acq_cnt := acq_cnt+1;
3822: END IF;
3823: IF l_contact_contains_str IS NOT NULL THEN
3824: acq_cnt := acq_cnt+1;
3825: END IF;
3826: IF l_contact_pt_contains_str IS NOT NULL THEN
3827: acq_cnt := acq_cnt+1;

Line 3826: IF l_contact_pt_contains_str IS NOT NULL THEN

3822: END IF;
3823: IF l_contact_contains_str IS NOT NULL THEN
3824: acq_cnt := acq_cnt+1;
3825: END IF;
3826: IF l_contact_pt_contains_str IS NOT NULL THEN
3827: acq_cnt := acq_cnt+1;
3828: END IF;
3829: IF acq_cnt>1 THEN
3830: other_acq_criteria_exists := TRUE;

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

3856: FND_MSG_PUB.ADD;
3857: RAISE FND_API.G_EXC_ERROR;
3858: END IF;
3859: END IF;
3860: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3861: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3862: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3863: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3864: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3857: RAISE FND_API.G_EXC_ERROR;
3858: END IF;
3859: END IF;
3860: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3861: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3862: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3863: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3864: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3865: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3858: END IF;
3859: END IF;
3860: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3861: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3862: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3863: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3864: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3865: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3866: END IF;

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

3859: END IF;
3860: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3861: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3862: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3863: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3864: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3865: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3866: END IF;
3867: END;

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

3860: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3861: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3862: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3863: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3864: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3865: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3866: END IF;
3867: END;
3868: /************ End of find_persons private procedures **********/

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

3861: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3862: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3863: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3864: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3865: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3866: END IF;
3867: END;
3868: /************ End of find_persons private procedures **********/
3869:

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

3896: g_score_until_thresh := false;
3897: END IF;
3898: -- ************************************************************
3899: -- STEP 2. Setup of intermedia query strings for Acquisition query
3900: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3901: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3902: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3903: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3904: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

3897: END IF;
3898: -- ************************************************************
3899: -- STEP 2. Setup of intermedia query strings for Acquisition query
3900: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3901: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3902: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3903: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3904: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3905:

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

3898: -- ************************************************************
3899: -- STEP 2. Setup of intermedia query strings for Acquisition query
3900: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3901: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3902: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3903: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3904: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3905:
3906: -- Setup Search Context ID

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

3899: -- STEP 2. Setup of intermedia query strings for Acquisition query
3900: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3901: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3902: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3903: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3904: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3905:
3906: -- Setup Search Context ID
3907: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3910: IF l_party_contains_str IS NULL THEN

3906: -- Setup Search Context ID
3907: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3908: x_search_ctx_id := l_search_ctx_id;
3909:
3910: IF l_party_contains_str IS NULL THEN
3911: defpt := 1;
3912: END IF;
3913: IF l_party_site_contains_str IS NULL THEN
3914: defps := 1;

Line 3913: IF l_party_site_contains_str IS NULL THEN

3909:
3910: IF l_party_contains_str IS NULL THEN
3911: defpt := 1;
3912: END IF;
3913: IF l_party_site_contains_str IS NULL THEN
3914: defps := 1;
3915: END IF;
3916: IF l_contact_contains_str IS NULL THEN
3917: defct := 1;

Line 3916: IF l_contact_contains_str IS NULL THEN

3912: END IF;
3913: IF l_party_site_contains_str IS NULL THEN
3914: defps := 1;
3915: END IF;
3916: IF l_contact_contains_str IS NULL THEN
3917: defct := 1;
3918: END IF;
3919: IF l_contact_pt_contains_str IS NULL THEN
3920: defcpt := 1;

Line 3919: IF l_contact_pt_contains_str IS NULL THEN

3915: END IF;
3916: IF l_contact_contains_str IS NULL THEN
3917: defct := 1;
3918: END IF;
3919: IF l_contact_pt_contains_str IS NULL THEN
3920: defcpt := 1;
3921: END IF;
3922:
3923: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3921: END IF;
3922:
3923: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3924: 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);
3925: 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);
3926: 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);
3927: 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);
3928: 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);
3929: 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 3926: 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);

3922:
3923: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3924: 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);
3925: 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);
3926: 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);
3927: 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);
3928: 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);
3929: 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);
3930: END IF;

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

3923: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3924: 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);
3925: 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);
3926: 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);
3927: 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);
3928: 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);
3929: 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);
3930: END IF;
3931: FOR I in 1..5 LOOP

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

3924: 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);
3925: 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);
3926: 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);
3927: 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);
3928: 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);
3929: 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);
3930: END IF;
3931: FOR I in 1..5 LOOP
3932: do_eval(I);

Line 3934: IF l_contact_contains_str IS NOT NULL THEN

3930: END IF;
3931: FOR I in 1..5 LOOP
3932: do_eval(I);
3933: END LOOP;
3934: IF l_contact_contains_str IS NOT NULL THEN
3935: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3936: END IF;
3937: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3938: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 28 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3931: FOR I in 1..5 LOOP
3932: do_eval(I);
3933: END LOOP;
3934: IF l_contact_contains_str IS NOT NULL THEN
3935: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3936: END IF;
3937: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3938: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 28 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3939: END IF;

Line 4737: l_party_contains_str VARCHAR2(32000);

4733: x_num_matches OUT NUMBER
4734: ) IS
4735:
4736: -- Strings to hold the generated Intermedia query strings
4737: l_party_contains_str VARCHAR2(32000);
4738: l_match_idx NUMBER;
4739: l_party_site_contains_str VARCHAR2(32000);
4740: l_contact_contains_str VARCHAR2(32000);
4741: l_contact_pt_contains_str VARCHAR2(32000);

Line 4739: l_party_site_contains_str VARCHAR2(32000);

4735:
4736: -- Strings to hold the generated Intermedia query strings
4737: l_party_contains_str VARCHAR2(32000);
4738: l_match_idx NUMBER;
4739: l_party_site_contains_str VARCHAR2(32000);
4740: l_contact_contains_str VARCHAR2(32000);
4741: l_contact_pt_contains_str VARCHAR2(32000);
4742: l_tmp VARCHAR2(32000);
4743:

Line 4740: l_contact_contains_str VARCHAR2(32000);

4736: -- Strings to hold the generated Intermedia query strings
4737: l_party_contains_str VARCHAR2(32000);
4738: l_match_idx NUMBER;
4739: l_party_site_contains_str VARCHAR2(32000);
4740: l_contact_contains_str VARCHAR2(32000);
4741: l_contact_pt_contains_str VARCHAR2(32000);
4742: l_tmp VARCHAR2(32000);
4743:
4744: -- Other local variables

Line 4741: l_contact_pt_contains_str VARCHAR2(32000);

4737: l_party_contains_str VARCHAR2(32000);
4738: l_match_idx NUMBER;
4739: l_party_site_contains_str VARCHAR2(32000);
4740: l_contact_contains_str VARCHAR2(32000);
4741: l_contact_pt_contains_str VARCHAR2(32000);
4742: l_tmp VARCHAR2(32000);
4743:
4744: -- Other local variables
4745: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4816: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4817:
4818: -- ************************************************************
4819: -- STEP 2. Setup of intermedia query strings for Acquisition query
4820: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4821: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4822: 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);
4823:
4824: -- Setup Search Context ID

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

4817:
4818: -- ************************************************************
4819: -- STEP 2. Setup of intermedia query strings for Acquisition query
4820: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4821: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4822: 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);
4823:
4824: -- Setup Search Context ID
4825: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4826: x_search_ctx_id := l_search_ctx_id;
4827:
4828: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4829: 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);
4830: 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);
4831: 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);
4832: 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);
4833: END IF;
4834: IF l_party_site_contains_str IS NULL THEN

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

4827:
4828: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4829: 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);
4830: 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);
4831: 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);
4832: 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);
4833: END IF;
4834: IF l_party_site_contains_str IS NULL THEN
4835: defps := 1;

Line 4834: IF l_party_site_contains_str IS NULL THEN

4830: 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);
4831: 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);
4832: 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);
4833: END IF;
4834: IF l_party_site_contains_str IS NULL THEN
4835: defps := 1;
4836: END IF;
4837: IF l_contact_pt_contains_str IS NULL THEN
4838: defcpt := 1;

Line 4837: IF l_contact_pt_contains_str IS NULL THEN

4833: END IF;
4834: IF l_party_site_contains_str IS NULL THEN
4835: defps := 1;
4836: END IF;
4837: IF l_contact_pt_contains_str IS NULL THEN
4838: defcpt := 1;
4839: END IF;
4840:
4841: IF l_party_site_contains_str IS NOT NULL THEN

Line 4841: IF l_party_site_contains_str IS NOT NULL THEN

4837: IF l_contact_pt_contains_str IS NULL THEN
4838: defcpt := 1;
4839: END IF;
4840:
4841: IF l_party_site_contains_str IS NOT NULL THEN
4842: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4843: LOOP
4844: FETCH l_party_site_cur INTO
4845: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;

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

4838: defcpt := 1;
4839: END IF;
4840:
4841: IF l_party_site_contains_str IS NOT NULL THEN
4842: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4843: LOOP
4844: FETCH l_party_site_cur INTO
4845: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
4846: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4865: IF l_contact_pt_contains_str IS NOT NULL THEN

4861: END LOOP;
4862: CLOSE l_party_site_cur;
4863: END IF;
4864:
4865: IF l_contact_pt_contains_str IS NOT NULL THEN
4866: SAVEPOINT eval_start;
4867: IF l_match_str = ' AND ' OR (
4868: ((l_cpt_max_score/l_entered_max_score)<(101/360))
4869: ) THEN

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

4869: ) THEN
4870: FORALL I in 1..l_match_ps_list.COUNT
4871: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4872: values (l_search_ctx_id,l_match_ps_list(I));
4873: 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');
4874: ELSE
4875: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4876: END IF;
4877: LOOP

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

4871: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4872: values (l_search_ctx_id,l_match_ps_list(I));
4873: 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');
4874: ELSE
4875: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4876: END IF;
4877: LOOP
4878: FETCH l_contact_pt_cur INTO
4879: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 4975: l_party_contains_str VARCHAR2(32000);

4971: x_num_matches OUT NUMBER
4972: ) IS
4973:
4974: -- Strings to hold the generated Intermedia query strings
4975: l_party_contains_str VARCHAR2(32000);
4976: l_party_site_contains_str VARCHAR2(32000);
4977: l_contact_contains_str VARCHAR2(32000);
4978: l_contact_pt_contains_str VARCHAR2(32000);
4979: l_tmp VARCHAR2(32000);

Line 4976: l_party_site_contains_str VARCHAR2(32000);

4972: ) IS
4973:
4974: -- Strings to hold the generated Intermedia query strings
4975: l_party_contains_str VARCHAR2(32000);
4976: l_party_site_contains_str VARCHAR2(32000);
4977: l_contact_contains_str VARCHAR2(32000);
4978: l_contact_pt_contains_str VARCHAR2(32000);
4979: l_tmp VARCHAR2(32000);
4980:

Line 4977: l_contact_contains_str VARCHAR2(32000);

4973:
4974: -- Strings to hold the generated Intermedia query strings
4975: l_party_contains_str VARCHAR2(32000);
4976: l_party_site_contains_str VARCHAR2(32000);
4977: l_contact_contains_str VARCHAR2(32000);
4978: l_contact_pt_contains_str VARCHAR2(32000);
4979: l_tmp VARCHAR2(32000);
4980:
4981: -- Other local variables

Line 4978: l_contact_pt_contains_str VARCHAR2(32000);

4974: -- Strings to hold the generated Intermedia query strings
4975: l_party_contains_str VARCHAR2(32000);
4976: l_party_site_contains_str VARCHAR2(32000);
4977: l_contact_contains_str VARCHAR2(32000);
4978: l_contact_pt_contains_str VARCHAR2(32000);
4979: l_tmp VARCHAR2(32000);
4980:
4981: -- Other local variables
4982: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5048: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5049:
5050: -- ************************************************************
5051: -- STEP 2. Setup of intermedia query strings for Acquisition query
5052: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5053: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5054: 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);
5055:
5056: -- Setup Search Context ID

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

5049:
5050: -- ************************************************************
5051: -- STEP 2. Setup of intermedia query strings for Acquisition query
5052: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5053: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5054: 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);
5055:
5056: -- Setup Search Context ID
5057: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5058: x_search_ctx_id := l_search_ctx_id;
5059:
5060: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5061: 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);
5062: 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);
5063: 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);
5064: 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);
5065: END IF;
5066: IF l_contact_contains_str IS NULL THEN

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

5059:
5060: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5061: 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);
5062: 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);
5063: 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);
5064: 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);
5065: END IF;
5066: IF l_contact_contains_str IS NULL THEN
5067: defct := 1;

Line 5066: IF l_contact_contains_str IS NULL THEN

5062: 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);
5063: 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);
5064: 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);
5065: END IF;
5066: IF l_contact_contains_str IS NULL THEN
5067: defct := 1;
5068: END IF;
5069: IF l_contact_pt_contains_str IS NULL THEN
5070: defcpt := 1;

Line 5069: IF l_contact_pt_contains_str IS NULL THEN

5065: END IF;
5066: IF l_contact_contains_str IS NULL THEN
5067: defct := 1;
5068: END IF;
5069: IF l_contact_pt_contains_str IS NULL THEN
5070: defcpt := 1;
5071: END IF;
5072:
5073: IF l_contact_contains_str IS NOT NULL THEN

Line 5073: IF l_contact_contains_str IS NOT NULL THEN

5069: IF l_contact_pt_contains_str IS NULL THEN
5070: defcpt := 1;
5071: END IF;
5072:
5073: IF l_contact_contains_str IS NOT NULL THEN
5074: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5075: LOOP
5076: FETCH l_contact_cur INTO
5077: l_org_contact_id, l_ct_party_id ;

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

5070: defcpt := 1;
5071: END IF;
5072:
5073: IF l_contact_contains_str IS NOT NULL THEN
5074: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5075: LOOP
5076: FETCH l_contact_cur INTO
5077: l_org_contact_id, l_ct_party_id ;
5078: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5095: IF l_contact_pt_contains_str IS NOT NULL THEN

5091: END LOOP;
5092: CLOSE l_contact_cur;
5093: END IF;
5094:
5095: IF l_contact_pt_contains_str IS NOT NULL THEN
5096: SAVEPOINT eval_start;
5097: IF l_match_str = ' AND ' OR (
5098: ((l_cpt_max_score/l_entered_max_score)<(101/360))
5099: ) THEN

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

5099: ) THEN
5100: FORALL I in 1..l_match_contact_list.COUNT
5101: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5102: values (l_search_ctx_id,l_match_contact_list(I));
5103: 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');
5104: ELSE
5105: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5106: END IF;
5107: LOOP

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

5101: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5102: values (l_search_ctx_id,l_match_contact_list(I));
5103: 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');
5104: ELSE
5105: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5106: END IF;
5107: LOOP
5108: FETCH l_contact_pt_cur INTO
5109: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 5203: l_contact_pt_contains_str VARCHAR2(32000);

5199: ) IS
5200:
5201:
5202: -- Strings to hold the generated Intermedia query strings
5203: l_contact_pt_contains_str VARCHAR2(32000);
5204: l_tmp VARCHAR2(32000);
5205:
5206: -- Other local variables
5207: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5260: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5261:
5262: -- ************************************************************
5263: -- STEP 2. Setup of intermedia query strings for Acquisition query
5264: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5265: 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);
5266:
5267: -- Setup Search Context ID
5268: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5268: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5269: x_search_ctx_id := l_search_ctx_id;
5270: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5271: 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);
5272: 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);
5273: 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);
5274: END IF;
5275:
5276: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 5276: IF l_contact_pt_contains_str IS NOT NULL THEN

5272: 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);
5273: 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);
5274: END IF;
5275:
5276: IF l_contact_pt_contains_str IS NOT NULL THEN
5277: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5278: cnt := 1;
5279: LOOP
5280: FETCH l_contact_pt_cur INTO

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

5273: 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);
5274: END IF;
5275:
5276: IF l_contact_pt_contains_str IS NOT NULL THEN
5277: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5278: cnt := 1;
5279: LOOP
5280: FETCH l_contact_pt_cur INTO
5281: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 5348: l_party_contains_str VARCHAR2(32000);

5344: x_search_ctx_id IN OUT NUMBER
5345: ) IS
5346:
5347: -- Strings to hold the generated Intermedia query strings
5348: l_party_contains_str VARCHAR2(32000);
5349: l_party_site_contains_str VARCHAR2(32000);
5350: l_contact_contains_str VARCHAR2(32000);
5351: l_contact_pt_contains_str VARCHAR2(32000);
5352: l_tmp VARCHAR2(32000);

Line 5349: l_party_site_contains_str VARCHAR2(32000);

5345: ) IS
5346:
5347: -- Strings to hold the generated Intermedia query strings
5348: l_party_contains_str VARCHAR2(32000);
5349: l_party_site_contains_str VARCHAR2(32000);
5350: l_contact_contains_str VARCHAR2(32000);
5351: l_contact_pt_contains_str VARCHAR2(32000);
5352: l_tmp VARCHAR2(32000);
5353:

Line 5350: l_contact_contains_str VARCHAR2(32000);

5346:
5347: -- Strings to hold the generated Intermedia query strings
5348: l_party_contains_str VARCHAR2(32000);
5349: l_party_site_contains_str VARCHAR2(32000);
5350: l_contact_contains_str VARCHAR2(32000);
5351: l_contact_pt_contains_str VARCHAR2(32000);
5352: l_tmp VARCHAR2(32000);
5353:
5354: -- Other local variables

Line 5351: l_contact_pt_contains_str VARCHAR2(32000);

5347: -- Strings to hold the generated Intermedia query strings
5348: l_party_contains_str VARCHAR2(32000);
5349: l_party_site_contains_str VARCHAR2(32000);
5350: l_contact_contains_str VARCHAR2(32000);
5351: l_contact_pt_contains_str VARCHAR2(32000);
5352: l_tmp VARCHAR2(32000);
5353:
5354: -- Other local variables
5355: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5423: g_score_until_thresh := false;
5424: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5425: -- ************************************************************
5426: -- STEP 2. Setup of intermedia query strings for Acquisition query
5427: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5428: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5429: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5430: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5431:

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

5424: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5425: -- ************************************************************
5426: -- STEP 2. Setup of intermedia query strings for Acquisition query
5427: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5428: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5429: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5430: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5431:
5432: -- Setup Search Context ID

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

5425: -- ************************************************************
5426: -- STEP 2. Setup of intermedia query strings for Acquisition query
5427: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5428: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5429: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5430: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5431:
5432: -- Setup Search Context ID
5433: IF x_search_ctx_id IS NULL THEN

Line 5449: IF l_party_site_contains_str IS NOT NULL THEN

5445: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX2,l_TX8,l_TX44,1);
5446: END LOOP;
5447: CLOSE l_party_cur;
5448:
5449: IF l_party_site_contains_str IS NOT NULL THEN
5450: l_max_score := 0;
5451: l_max_id := 0;
5452: l_max_idx := 0;
5453: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5450: l_max_score := 0;
5451: l_max_id := 0;
5452: l_max_idx := 0;
5453: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5454: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5455: ELSE
5456: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5457: END IF;
5458: LOOP

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

5452: l_max_idx := 0;
5453: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5454: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5455: ELSE
5456: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5457: END IF;
5458: LOOP
5459: FETCH l_party_site_cur INTO
5460: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX11;

Line 5478: IF l_contact_contains_str IS NOT NULL THEN

5474: 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_TX3,l_max_TX4,l_max_TX11,l_max_idx);
5475: END IF;
5476: END IF;
5477:
5478: IF l_contact_contains_str IS NOT NULL THEN
5479: l_max_score := 0;
5480: l_max_id := 0;
5481: l_max_idx := 0;
5482: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5478: IF l_contact_contains_str IS NOT NULL THEN
5479: l_max_score := 0;
5480: l_max_id := 0;
5481: l_max_idx := 0;
5482: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5483: LOOP
5484: FETCH l_contact_cur INTO
5485: l_org_contact_id, l_ct_party_id ;
5486: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5500: IF l_contact_pt_contains_str IS NOT NULL THEN

5496: 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);
5497: END IF;
5498: END IF;
5499:
5500: IF l_contact_pt_contains_str IS NOT NULL THEN
5501: l_max_score := 0;
5502: l_max_id := 0;
5503: l_max_idx := 0;
5504: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5501: l_max_score := 0;
5502: l_max_id := 0;
5503: l_max_idx := 0;
5504: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5505: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5506: ELSE
5507: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5508: END IF;
5509: LOOP

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

5503: l_max_idx := 0;
5504: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5505: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5506: ELSE
5507: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5508: END IF;
5509: LOOP
5510: FETCH l_contact_pt_cur INTO
5511: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;