[Home] [Help]
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;
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
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
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;
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:
1027: p_party_max_score NUMBER,
1028: p_denorm_max_score NUMBER,
1029: p_non_denorm_max_score NUMBER,
1030: p_threshold NUMBER) RETURN VARCHAR2 IS
1031: l_party_contains_str VARCHAR2(32000);
1032: l_party_filter VARCHAR2(1) := null;
1033: l_prim_temp VARCHAR2(4000);
1034: l_denorm_str VARCHAR2(4000);
1035: BEGIN
1062: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1063: hz_utility_v2pub.debug(p_message=>'calculated denorm string l_denorm_str is '||l_denorm_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1064: END IF;
1065: END IF;
1066: IF lengthb(l_party_contains_str) > 4000 THEN
1067: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1068: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1069: FND_MSG_PUB.ADD;
1070: RAISE FND_API.G_EXC_ERROR;
1068: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1069: FND_MSG_PUB.ADD;
1070: RAISE FND_API.G_EXC_ERROR;
1071: END IF;
1072: IF (p_match_str = ' AND ' OR p_party_max_score
1074: 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);
1075: END IF;
1076: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1070: RAISE FND_API.G_EXC_ERROR;
1071: END IF;
1072: IF (p_match_str = ' AND ' OR p_party_max_score
1074: 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);
1075: END IF;
1076: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1077: 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);
1078: END IF;
1073: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1074: 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);
1075: END IF;
1076: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1077: 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);
1078: END IF;
1079: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1080: 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);
1081: END IF;
1078: END IF;
1079: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1080: 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);
1081: END IF;
1082: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1083: ELSE
1084: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1085: 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);
1086: 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);
1081: END IF;
1082: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1083: ELSE
1084: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1085: 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);
1086: 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);
1087: END IF;
1088: RETURN l_party_contains_str;
1089: END IF;
1082: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1083: ELSE
1084: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1085: 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);
1086: 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);
1087: END IF;
1088: RETURN l_party_contains_str;
1089: END IF;
1090: END;
1084: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1085: 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);
1086: 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);
1087: END IF;
1088: RETURN l_party_contains_str;
1089: END IF;
1090: END;
1091: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1092: l_contains_str VARCHAR2(32000);
1088: RETURN l_party_contains_str;
1089: END IF;
1090: END;
1091: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1092: l_contains_str VARCHAR2(32000);
1093: l_contains_str_temp VARCHAR2(32000);
1094: l_den_contains_str VARCHAR2(32000);
1095: l_den_contains_str_temp VARCHAR2(32000);
1096: l_filter_str VARCHAR2(4000) := null;
1089: END IF;
1090: END;
1091: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1092: l_contains_str VARCHAR2(32000);
1093: l_contains_str_temp VARCHAR2(32000);
1094: l_den_contains_str VARCHAR2(32000);
1095: l_den_contains_str_temp VARCHAR2(32000);
1096: l_filter_str VARCHAR2(4000) := null;
1097: l_prim_temp VARCHAR2(4000) := null;
1090: END;
1091: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1092: l_contains_str VARCHAR2(32000);
1093: l_contains_str_temp VARCHAR2(32000);
1094: l_den_contains_str VARCHAR2(32000);
1095: l_den_contains_str_temp VARCHAR2(32000);
1096: l_filter_str VARCHAR2(4000) := null;
1097: l_prim_temp VARCHAR2(4000) := null;
1098: l_prim_temp_den VARCHAR2(4000) := null;
1091: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1092: l_contains_str VARCHAR2(32000);
1093: l_contains_str_temp VARCHAR2(32000);
1094: l_den_contains_str VARCHAR2(32000);
1095: l_den_contains_str_temp VARCHAR2(32000);
1096: l_filter_str VARCHAR2(4000) := null;
1097: l_prim_temp VARCHAR2(4000) := null;
1098: l_prim_temp_den VARCHAR2(4000) := null;
1099: BEGIN
1101: 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);
1102: END IF;
1103: x_denorm_str := NULL;
1104:
1105: -- Dynamic setup of party site contains str
1106: --
1107: -- For each primary transformation add to intermedia query if it
1108: -- is not null
1109: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1106: --
1107: -- For each primary transformation add to intermedia query if it
1108: -- is not null
1109: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1110: l_contains_str_temp := null;
1111: l_den_contains_str_temp := null;
1112: l_filter_str := null;
1113:
1114: -- Setup query string for ADDRESS
1107: -- For each primary transformation add to intermedia query if it
1108: -- is not null
1109: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1110: l_contains_str_temp := null;
1111: l_den_contains_str_temp := null;
1112: l_filter_str := null;
1113:
1114: -- Setup query string for ADDRESS
1115: l_prim_temp := null;
1115: l_prim_temp := null;
1116: l_prim_temp_den := null;
1117: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1118: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1119: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1120:
1121: -- Setup query string for CITY
1122: l_prim_temp := null;
1123: l_prim_temp_den := null;
1124: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1125: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1126: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1127: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1128: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1129: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1130:
1131: -- Setup query string for POSTAL_CODE
1132: l_prim_temp := null;
1125: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1126: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1127: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1128: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1129: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1130:
1131: -- Setup query string for POSTAL_CODE
1132: l_prim_temp := null;
1133: l_prim_temp_den := null;
1134: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1135: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1136: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1137: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1138: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1139: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1140:
1141: -- Setup query string for PROVINCE
1142: l_prim_temp := null;
1135: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1136: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1137: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1138: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1139: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1140:
1141: -- Setup query string for PROVINCE
1142: l_prim_temp := null;
1143: l_prim_temp_den := null;
1144: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1145: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1146: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1147: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
1148: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1149: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1150:
1151: -- Setup query string for STATE
1152: l_prim_temp := null;
1145: g_party_site_stage_list(I).TX13,'A31',l_prim_temp);
1146: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1147: g_party_site_stage_list(I).TX13,'D_PS',l_prim_temp_den);
1148: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1149: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1150:
1151: -- Setup query string for STATE
1152: l_prim_temp := null;
1153: l_prim_temp_den := null;
1154: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1155: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1156: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1157: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1158: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1159: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1160:
1161: -- Setup query string for COUNTY
1162: l_prim_temp := null;
1155: g_party_site_stage_list(I).TX15,'A32',l_prim_temp);
1156: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE + WRSTATE
1157: g_party_site_stage_list(I).TX15,'D_PS',l_prim_temp_den);
1158: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1159: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1160:
1161: -- Setup query string for COUNTY
1162: l_prim_temp := null;
1163: l_prim_temp_den := null;
1164: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1165: g_party_site_stage_list(I).TX21,'A35',l_prim_temp);
1166: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1167: g_party_site_stage_list(I).TX21,'D_PS',l_prim_temp_den);
1168: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1169: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1170:
1171: -- Setup query string for COUNTRY
1172: l_prim_temp := null;
1165: g_party_site_stage_list(I).TX21,'A35',l_prim_temp);
1166: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1167: g_party_site_stage_list(I).TX21,'D_PS',l_prim_temp_den);
1168: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1169: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1170:
1171: -- Setup query string for COUNTRY
1172: l_prim_temp := null;
1173: l_prim_temp_den := null;
1174: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1175: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1176: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1177: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1178: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1179: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1180:
1181: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1182: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1175: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1176: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1177: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1178: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1179: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1180:
1181: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1182: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1183: END LOOP;
1177: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1178: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1179: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1180:
1181: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1182: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1183: END LOOP;
1184: -- Add the search criteria to query string
1185: IF lengthb(l_contains_str) > 4000 THEN
1178: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1179: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1180:
1181: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1182: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1183: END LOOP;
1184: -- Add the search criteria to query string
1185: IF lengthb(l_contains_str) > 4000 THEN
1186: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1181: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1182: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1183: END LOOP;
1184: -- Add the search criteria to query string
1185: IF lengthb(l_contains_str) > 4000 THEN
1186: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1187: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1188: FND_MSG_PUB.ADD;
1189: RAISE FND_API.G_EXC_ERROR;
1187: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1188: FND_MSG_PUB.ADD;
1189: RAISE FND_API.G_EXC_ERROR;
1190: END IF;
1191: x_denorm_str := l_den_contains_str;
1192: RETURN l_contains_str;
1193: END;
1194:
1195: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1188: FND_MSG_PUB.ADD;
1189: RAISE FND_API.G_EXC_ERROR;
1190: END IF;
1191: x_denorm_str := l_den_contains_str;
1192: RETURN l_contains_str;
1193: END;
1194:
1195: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1196: l_contains_str VARCHAR2(32000);
1192: RETURN l_contains_str;
1193: END;
1194:
1195: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1196: l_contains_str VARCHAR2(32000);
1197: l_contains_str_temp VARCHAR2(32000);
1198: l_den_contains_str VARCHAR2(32000);
1199: l_den_contains_str_temp VARCHAR2(32000);
1200: l_filter_str VARCHAR2(4000) := null;
1193: END;
1194:
1195: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1196: l_contains_str VARCHAR2(32000);
1197: l_contains_str_temp VARCHAR2(32000);
1198: l_den_contains_str VARCHAR2(32000);
1199: l_den_contains_str_temp VARCHAR2(32000);
1200: l_filter_str VARCHAR2(4000) := null;
1201: l_prim_temp VARCHAR2(4000) := null;
1194:
1195: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1196: l_contains_str VARCHAR2(32000);
1197: l_contains_str_temp VARCHAR2(32000);
1198: l_den_contains_str VARCHAR2(32000);
1199: l_den_contains_str_temp VARCHAR2(32000);
1200: l_filter_str VARCHAR2(4000) := null;
1201: l_prim_temp VARCHAR2(4000) := null;
1202: l_prim_temp_den VARCHAR2(4000) := null;
1195: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1196: l_contains_str VARCHAR2(32000);
1197: l_contains_str_temp VARCHAR2(32000);
1198: l_den_contains_str VARCHAR2(32000);
1199: l_den_contains_str_temp VARCHAR2(32000);
1200: l_filter_str VARCHAR2(4000) := null;
1201: l_prim_temp VARCHAR2(4000) := null;
1202: l_prim_temp_den VARCHAR2(4000) := null;
1203: BEGIN
1208: RETURN NULL;
1209: END;
1210:
1211: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1212: l_contains_str VARCHAR2(32000);
1213: l_contains_str_temp VARCHAR2(32000);
1214: l_den_contains_str VARCHAR2(32000);
1215: l_den_contains_str_temp VARCHAR2(32000);
1216: l_filter_str VARCHAR2(4000) := null;
1209: END;
1210:
1211: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1212: l_contains_str VARCHAR2(32000);
1213: l_contains_str_temp VARCHAR2(32000);
1214: l_den_contains_str VARCHAR2(32000);
1215: l_den_contains_str_temp VARCHAR2(32000);
1216: l_filter_str VARCHAR2(4000) := null;
1217: l_prim_temp VARCHAR2(4000) := null;
1210:
1211: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1212: l_contains_str VARCHAR2(32000);
1213: l_contains_str_temp VARCHAR2(32000);
1214: l_den_contains_str VARCHAR2(32000);
1215: l_den_contains_str_temp VARCHAR2(32000);
1216: l_filter_str VARCHAR2(4000) := null;
1217: l_prim_temp VARCHAR2(4000) := null;
1218: l_prim_temp_den VARCHAR2(4000) := null;
1211: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1212: l_contains_str VARCHAR2(32000);
1213: l_contains_str_temp VARCHAR2(32000);
1214: l_den_contains_str VARCHAR2(32000);
1215: l_den_contains_str_temp VARCHAR2(32000);
1216: l_filter_str VARCHAR2(4000) := null;
1217: l_prim_temp VARCHAR2(4000) := null;
1218: l_prim_temp_den VARCHAR2(4000) := null;
1219: BEGIN
1228: p_dup_party_id NUMBER,
1229: p_restrict_sql VARCHAR2,
1230: p_match_str VARCHAR2,
1231: p_search_merged VARCHAR2,
1232: p_party_contains_str VARCHAR2,
1233: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1234: l_sqlstr VARCHAR2(4000);
1235: l_search_merged VARCHAR2(1);
1236: BEGIN
1245: END IF;
1246: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1247: 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);
1248: END IF;
1249: 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
1250: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1251: 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);
1252: END IF;
1253: OPEN x_cursor FOR
1274: l_sqlstr := l_sqlstr || ' AND (:p_dup IS NULL OR party_id <> :p_dup ) ';
1275: IF g_other_party_level_attribs THEN
1276: NULL;
1277: END IF;
1278: 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
1279: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1280: 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);
1281: END IF;
1282: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1276: NULL;
1277: END IF;
1278: 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
1279: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1280: 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);
1281: END IF;
1282: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1283: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1284: l_sqlstr := l_sqlstr || ' )) ';
1312: END IF;
1313: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1314: 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);
1315: END IF;
1316: 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
1317: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1318: 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);
1319: END IF;
1320: 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
1314: 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);
1315: END IF;
1316: 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
1317: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1318: 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);
1319: END IF;
1320: 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
1321: ,g_party_site_stage_list(1).TX10
1322: ,g_party_site_stage_list(1).TX10
1336: hz_utility_v2pub.debug(p_message=>'ELSIF g_other_party_level_attribs THEN',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1337: END IF;
1338: 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
1339: ;
1340: 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
1341: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1342: 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);
1343: END IF;
1344: 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
1338: 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
1339: ;
1340: 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
1341: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1342: 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);
1343: END IF;
1344: 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
1345: ,g_party_site_stage_list(1).TX10
1346: ,g_party_site_stage_list(1).TX10
1365: END;
1366: PROCEDURE open_party_cursor(
1367: p_dup_party_id NUMBER,
1368: p_restrict_sql VARCHAR2,
1369: p_contains_str VARCHAR2,
1370: p_search_ctx_id NUMBER,
1371: p_match_str VARCHAR2,
1372: p_search_merged VARCHAR2,
1373: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1384: l_search_merged := 'N';
1385: ELSE
1386: l_search_merged := p_search_merged;
1387: END IF;
1388: IF p_contains_str IS NULL THEN
1389: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1390: 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);
1391: END IF;
1392: OPEN x_cursor FOR
1386: l_search_merged := p_search_merged;
1387: END IF;
1388: IF p_contains_str IS NULL THEN
1389: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1390: 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);
1391: END IF;
1392: OPEN x_cursor FOR
1393: SELECT PARTY_ID
1394: FROM HZ_STAGED_PARTIES stage
1403: END IF;
1404: OPEN x_cursor FOR
1405: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID
1406: FROM HZ_STAGED_PARTIES stage
1407: WHERE contains( concat_col, p_contains_str)>0
1408: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1409: AND( (l_search_merged ='Y' )
1410: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1411: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
1416: END IF;
1417: OPEN x_cursor FOR
1418: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID
1419: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1420: WHERE contains( concat_col, p_contains_str)>0
1421: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1422: AND d.party_id = stage.party_id
1423: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1424: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1441: END IF;
1442: IF p_search_ctx_id IS NULL THEN
1443: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID '||
1444: ' FROM HZ_STAGED_PARTIES stage'||
1445: ' WHERE contains( concat_col, :cont)>0'||
1446: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1447: ' AND ('||p_restrict_sql||')' ||
1448: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1449: IF l_search_merged = 'Y' THEN
1453: ELSE
1454: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1455: END IF;
1456: output_long_strings('----------------------------------------------------------');
1457: output_long_strings('Party Contains String = '||p_contains_str);
1458: output_long_strings('Restrict Sql = '||p_restrict_sql);
1459: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1460: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1461: ,p_dup_party_id, p_dup_party_id;
1455: END IF;
1456: output_long_strings('----------------------------------------------------------');
1457: output_long_strings('Party Contains String = '||p_contains_str);
1458: output_long_strings('Restrict Sql = '||p_restrict_sql);
1459: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1460: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1461: ,p_dup_party_id, p_dup_party_id;
1462: END IF;
1463: END IF;
1486: PROCEDURE open_party_site_cursor(
1487: p_dup_party_id NUMBER,
1488: p_party_id NUMBER,
1489: p_restrict_sql VARCHAR2,
1490: p_contains_str VARCHAR2,
1491: p_search_ctx_id NUMBER,
1492: p_search_merged VARCHAR2,
1493: p_search_rel_sites VARCHAR2,
1494: p_person_api VARCHAR2,
1518: IF p_search_rel_sites = 'N' THEN
1519: OPEN x_cursor FOR
1520: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1521: FROM HZ_STAGED_PARTY_SITES stage
1522: WHERE contains( concat_col, p_contains_str)>0
1523: AND( (l_search_merged ='Y' )
1524: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1525: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1526: AND stage.party_id = p_party_id;
1527: ELSE
1528: OPEN x_cursor FOR
1529: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1530: FROM HZ_STAGED_PARTY_SITES stage
1531: WHERE contains( concat_col, p_contains_str)>0
1532: AND( (l_search_merged ='Y' )
1533: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1534: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1535: AND stage.party_id = p_party_id
1535: AND stage.party_id = p_party_id
1536: UNION
1537: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1538: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1539: WHERE contains( concat_col, p_contains_str)>0
1540: AND( (l_search_merged ='Y' )
1541: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1542: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1543: AND r.object_id = p_party_id
1557: END IF;
1558: OPEN x_cursor FOR
1559: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1560: FROM HZ_STAGED_PARTY_SITES stage
1561: WHERE contains( concat_col, p_contains_str)>0
1562: AND EXISTS (
1563: SELECT 1 FROM HZ_STAGED_PARTIES p
1564: WHERE p.PARTY_ID = stage.PARTY_ID
1565: AND( (l_search_merged ='Y' )
1577: IF p_person_api = 'Y' THEN
1578: OPEN x_cursor FOR
1579: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1580: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1581: WHERE contains( concat_col, p_contains_str)>0
1582: AND( (l_search_merged ='Y' )
1583: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1584: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1585: AND d.search_context_id = p_search_ctx_id
1587: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1588: UNION
1589: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1590: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1591: WHERE contains( concat_col, p_contains_str)>0
1592: AND( (l_search_merged ='Y' )
1593: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1594: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1595: AND d.search_context_id = p_search_ctx_id
1600: ELSE
1601: OPEN x_cursor FOR
1602: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22
1603: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1604: WHERE contains( concat_col, p_contains_str)>0
1605: AND( (l_search_merged ='Y' )
1606: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1607: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1608: AND d.search_context_id = p_search_ctx_id
1645: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1646: END IF;
1647: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1648: ' FROM HZ_STAGED_PARTY_SITES stage'||
1649: ' WHERE contains( concat_col, :cont)>0'||
1650: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1651: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1652: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1653: ' AND (ORG_CONTACT_ID IS NULL ' ||
1655: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1656: ' UNION ' ||
1657: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1658: ' FROM HZ_STAGED_PARTY_SITES stage'||
1659: ' WHERE contains( concat_col, :cont)>0'||
1660: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1661: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1662: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1663: ' AND ORG_CONTACT_ID IN ' ||
1665: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1666: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1667: ' and ('||p_restrict_sql1|| ') )' ||
1668: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1669: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1670: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1671: ELSE
1672: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1673: ' FROM HZ_STAGED_PARTY_SITES stage'||
1666: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1667: ' and ('||p_restrict_sql1|| ') )' ||
1668: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1669: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1670: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1671: ELSE
1672: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1673: ' FROM HZ_STAGED_PARTY_SITES stage'||
1674: ' WHERE contains( concat_col, :cont)>0'||
1670: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1671: ELSE
1672: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1673: ' FROM HZ_STAGED_PARTY_SITES stage'||
1674: ' WHERE contains( concat_col, :cont)>0'||
1675: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1676: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1677: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1678: ' AND EXISTS ('||
1680: ' WHERE p.party_id = stage.party_id ' ||
1681: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1682: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1683: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1684: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1685: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1686: ,p_dup_party_id, p_dup_party_id;
1687: END IF;
1688: ELSE
1687: END IF;
1688: ELSE
1689: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX20, TX21, TX22'||
1690: ' FROM HZ_STAGED_PARTY_SITES stage'||
1691: ' WHERE contains( concat_col, :cont)>0'||
1692: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1693: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1694: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1695: ' AND EXISTS ('||
1697: ' WHERE p.party_id = stage.party_id ' ||
1698: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1699: ' AND ('||p_restrict_sql||')' ||
1700: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1701: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1702: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1703: ,p_dup_party_id, p_dup_party_id;
1704: END IF;
1705: END IF;
1703: ,p_dup_party_id, p_dup_party_id;
1704: END IF;
1705: END IF;
1706: output_long_strings('----------------------------------------------------------');
1707: output_long_strings('Party Site Contains String = '||p_contains_str);
1708: output_long_strings('Restrict Sql = '||p_restrict_sql);
1709: exception
1710: when others then
1711: if (instrb(SQLERRM,'DRG-51030')>0) then
1731: PROCEDURE open_contact_cursor(
1732: p_dup_party_id NUMBER,
1733: p_party_id NUMBER,
1734: p_restrict_sql VARCHAR2,
1735: p_contains_str VARCHAR2,
1736: p_search_ctx_id NUMBER,
1737: p_search_merged VARCHAR2,
1738: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1739: l_sqlstr VARCHAR2(4000);
1759: END IF;
1760: OPEN x_cursor FOR
1761: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1762: FROM HZ_STAGED_CONTACTS stage
1763: WHERE contains( concat_col, p_contains_str)>0
1764: AND EXISTS (
1765: SELECT 1 FROM HZ_STAGED_PARTIES p
1766: WHERE p.PARTY_ID = stage.PARTY_ID
1767: AND( (l_search_merged ='Y' )
1782: END IF;
1783: OPEN x_cursor FOR
1784: SELECT ORG_CONTACT_ID, PARTY_ID
1785: FROM HZ_STAGED_CONTACTS stage
1786: WHERE contains( concat_col, p_contains_str)>0
1787: AND EXISTS (
1788: SELECT 1 FROM HZ_STAGED_PARTIES p
1789: WHERE p.PARTY_ID = stage.PARTY_ID
1790: AND( (l_search_merged ='Y' )
1801: END IF;
1802: OPEN x_cursor FOR
1803: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1804: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1805: WHERE contains( concat_col, p_contains_str)>0
1806: AND d.search_context_id = p_search_ctx_id
1807: AND d.party_id = stage.party_id
1808: AND( (l_search_merged ='Y' )
1809: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1835: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1836: END IF;
1837: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1838: ' FROM HZ_STAGED_CONTACTS stage'||
1839: ' WHERE contains( concat_col, :cont)>0'||
1840: ' AND EXISTS ('||
1841: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1842: ' WHERE p.party_id = stage.party_id ' ||
1843: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1845: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1846: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1847: ' AND ('||p_restrict_sql||')' ||
1848: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1849: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1850: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1851: ,p_dup_party_id, p_dup_party_id;
1852: END IF;
1853: output_long_strings('----------------------------------------------------------');
1850: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1851: ,p_dup_party_id, p_dup_party_id;
1852: END IF;
1853: output_long_strings('----------------------------------------------------------');
1854: output_long_strings('Contacts Contains String = '||p_contains_str);
1855: output_long_strings('Restrict Sql = '||p_restrict_sql);
1856: exception
1857: when others then
1858: if (instrb(SQLERRM,'DRG-51030')>0) then
1878: PROCEDURE open_contact_pt_cursor(
1879: p_dup_party_id NUMBER,
1880: p_party_id NUMBER,
1881: p_restrict_sql VARCHAR2,
1882: p_contains_str VARCHAR2,
1883: p_search_ctx_id NUMBER,
1884: p_search_merged VARCHAR2,
1885: p_search_rel_cpts VARCHAR2,
1886: p_person_api VARCHAR2,
1908: THEN
1909: OPEN x_cursor FOR
1910: 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
1911: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1912: WHERE contains( concat_col, p_contains_str)>0
1913: AND d.search_context_id = p_search_ctx_id
1914: AND( (l_search_merged ='Y' )
1915: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1916: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1920: THEN
1921: OPEN x_cursor FOR
1922: 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
1923: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1924: WHERE contains( concat_col, p_contains_str)>0
1925: AND d.search_context_id = p_search_ctx_id
1926: AND( (l_search_merged ='Y' )
1927: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1928: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1937: IF p_search_rel_cpts = 'N' THEN
1938: OPEN x_cursor FOR
1939: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1940: FROM HZ_STAGED_CONTACT_POINTS stage
1941: WHERE contains( concat_col, p_contains_str)>0
1942: AND( (l_search_merged ='Y' )
1943: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1944: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1945: AND stage.party_id = p_party_id;
1946: ELSE
1947: OPEN x_cursor FOR
1948: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1949: FROM HZ_STAGED_CONTACT_POINTS stage
1950: WHERE contains( concat_col, p_contains_str)>0
1951: AND( (l_search_merged ='Y' )
1952: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1953: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1954: AND stage.party_id = p_party_id
1954: AND stage.party_id = p_party_id
1955: UNION
1956: 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
1957: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1958: WHERE contains( concat_col, p_contains_str)>0
1959: AND r.object_id = p_party_id
1960: AND( (l_search_merged ='Y' )
1961: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1962: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1976: END IF;
1977: OPEN x_cursor FOR
1978: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1979: FROM HZ_STAGED_CONTACT_POINTS stage
1980: WHERE contains( concat_col, p_contains_str)>0
1981: AND EXISTS (
1982: SELECT 1 FROM HZ_STAGED_PARTIES p
1983: WHERE p.PARTY_ID = stage.PARTY_ID
1984: AND( (l_search_merged ='Y' )
1996: IF p_person_api = 'Y' THEN
1997: OPEN x_cursor FOR
1998: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1999: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2000: WHERE contains( concat_col, p_contains_str)>0
2001: AND d.search_context_id = p_search_ctx_id
2002: AND d.party_id = stage.party_id
2003: AND( (l_search_merged ='Y' )
2004: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2006: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2007: UNION
2008: 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
2009: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2010: WHERE contains( concat_col, p_contains_str)>0
2011: AND d.search_context_id = p_search_ctx_id
2012: AND d.party_id = r.subject_id
2013: AND r.relationship_id = oc.party_relationship_id
2014: AND oc.org_contact_id = stage.org_contact_id
2019: ELSE
2020: OPEN x_cursor FOR
2021: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2022: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2023: WHERE contains( concat_col, p_contains_str)>0
2024: AND d.search_context_id = p_search_ctx_id
2025: AND d.party_id = stage.party_id
2026: AND( (l_search_merged ='Y' )
2027: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2064: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2065: END IF;
2066: 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 '||
2067: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2068: ' WHERE contains( concat_col, :cont)>0 '||
2069: ' AND (stage.org_contact_id is null '||
2070: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2071: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2072: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2074: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2075: ' UNION ' ||
2076: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2077: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2078: ' WHERE contains( concat_col, :cont)>0 '||
2079: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2080: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2081: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2082: ' AND (stage.org_contact_id in ' ||
2084: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2085: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2086: ' and ('||p_restrict_sql1 || ') ) )' ||
2087: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2088: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2089: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2090: ELSE
2091: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2092: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2085: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2086: ' and ('||p_restrict_sql1 || ') ) )' ||
2087: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2088: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2089: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2090: ELSE
2091: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2092: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2093: ' WHERE contains( concat_col, :cont)>0'||
2089: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2090: ELSE
2091: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2092: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2093: ' WHERE contains( concat_col, :cont)>0'||
2094: ' AND EXISTS ('||
2095: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2096: ' WHERE p.party_id = stage.party_id ' ||
2097: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2099: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2100: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2101: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2102: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2103: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2104: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2105: ,p_dup_party_id, p_dup_party_id;
2106: END IF;
2107: ELSE
2106: END IF;
2107: ELSE
2108: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2109: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2110: ' WHERE contains( concat_col, :cont)>0'||
2111: ' AND EXISTS ('||
2112: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2113: ' WHERE p.party_id = stage.party_id ' ||
2114: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
2116: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2117: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2118: ' AND ('||p_restrict_sql||')' ||
2119: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2120: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2121: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2122: ,p_dup_party_id, p_dup_party_id;
2123: END IF;
2124: END IF;
2123: END IF;
2124: END IF;
2125: END IF;
2126: output_long_strings('----------------------------------------------------------');
2127: output_long_strings('Contact Points Contains String = '||p_contains_str);
2128: output_long_strings('Restrict Sql = '||p_restrict_sql);
2129: exception
2130: when others then
2131: if (instrb(SQLERRM,'DRG-51030')>0) then
2244: x_num_matches OUT NUMBER
2245: ) IS
2246:
2247: -- Strings to hold the generated Intermedia query strings
2248: l_party_contains_str VARCHAR2(32000);
2249: l_party_site_contains_str VARCHAR2(32000);
2250: l_contact_contains_str VARCHAR2(32000);
2251: l_contact_pt_contains_str VARCHAR2(32000);
2252: l_denorm_str VARCHAR2(32000);
2245: ) IS
2246:
2247: -- Strings to hold the generated Intermedia query strings
2248: l_party_contains_str VARCHAR2(32000);
2249: l_party_site_contains_str VARCHAR2(32000);
2250: l_contact_contains_str VARCHAR2(32000);
2251: l_contact_pt_contains_str VARCHAR2(32000);
2252: l_denorm_str VARCHAR2(32000);
2253: l_ps_denorm_str VARCHAR2(32000);
2246:
2247: -- Strings to hold the generated Intermedia query strings
2248: l_party_contains_str VARCHAR2(32000);
2249: l_party_site_contains_str VARCHAR2(32000);
2250: l_contact_contains_str VARCHAR2(32000);
2251: l_contact_pt_contains_str VARCHAR2(32000);
2252: l_denorm_str VARCHAR2(32000);
2253: l_ps_denorm_str VARCHAR2(32000);
2254: l_ct_denorm_str VARCHAR2(32000);
2247: -- Strings to hold the generated Intermedia query strings
2248: l_party_contains_str VARCHAR2(32000);
2249: l_party_site_contains_str VARCHAR2(32000);
2250: l_contact_contains_str VARCHAR2(32000);
2251: l_contact_pt_contains_str VARCHAR2(32000);
2252: l_denorm_str VARCHAR2(32000);
2253: l_ps_denorm_str VARCHAR2(32000);
2254: l_ct_denorm_str VARCHAR2(32000);
2255: l_cpt_denorm_str VARCHAR2(32000);
2344: call_type(2) := 'OR';
2345: END;
2346:
2347: /** Private procedure to acquire and score at party level ***/
2348: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2349: l_party_id_idx NUMBER:=1;
2350: l_ctx_id NUMBER;
2351: l_precalc_score BOOLEAN := FALSE;
2352: l_TX35_new varchar2(4000);
2376: l_search_ctx_id,H_PARTY_ID(I));
2377: l_party_id_idx := H_PARTY_ID.COUNT+1;
2378: ELSE
2379: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2380: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2381: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2382: 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);
2383: END IF;
2384: 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
2384: 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
2385: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2386: 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);
2387: END IF;
2388: IF (l_party_site_contains_str IS NULL AND
2389: l_contact_contains_str IS NULL AND
2390: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2391: 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);
2392: RAISE L_RETURN_IMM_EXC;
2385: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2386: 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);
2387: END IF;
2388: IF (l_party_site_contains_str IS NULL AND
2389: l_contact_contains_str IS NULL AND
2390: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2391: 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);
2392: RAISE L_RETURN_IMM_EXC;
2393: ELSE
2386: 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);
2387: END IF;
2388: IF (l_party_site_contains_str IS NULL AND
2389: l_contact_contains_str IS NULL AND
2390: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2391: 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);
2392: RAISE L_RETURN_IMM_EXC;
2393: ELSE
2394: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2390: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2391: 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);
2392: RAISE L_RETURN_IMM_EXC;
2393: ELSE
2394: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2395: END IF;
2396: ELSE
2397: IF p_index>1 THEN
2398: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2413: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2414: 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);
2415: END IF;
2416: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2417: 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);
2418: END IF;
2419: LOOP
2420: FETCH l_party_cur INTO
2421: l_party_id ;
2435: l_party_id_idx:= l_party_id_idx+1;
2436: END IF;
2437: IF (l_party_id_idx-1)>l_max_thresh THEN
2438: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2439: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2440: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2441: AND l_contact_pt_contains_str IS NULL THEN
2442: EXIT;
2443: END IF;
2437: IF (l_party_id_idx-1)>l_max_thresh THEN
2438: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2439: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2440: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2441: AND l_contact_pt_contains_str IS NULL THEN
2442: EXIT;
2443: END IF;
2444: CLOSE l_party_cur;
2445: IF p_index>1 THEN
2466: ROLLBACK to eval_start;
2467: END;
2468:
2469: /** Private procedure to acquire and score at party site level ***/
2470: 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
2471: l_party_id_idx NUMBER:=1;
2472: l_ctx_id NUMBER;
2473: h_ps_id HZ_PARTY_SEARCH.IDList;
2474: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2478: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2479: 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);
2480: 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);
2481: END IF;
2482: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2483: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2484: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2485: l_party_id := H_SCORES.FIRST;
2486: WHILE l_party_id IS NOT NULL LOOP
2479: 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);
2480: 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);
2481: END IF;
2482: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2483: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2484: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2485: l_party_id := H_SCORES.FIRST;
2486: WHILE l_party_id IS NOT NULL LOOP
2487: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2480: 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);
2481: END IF;
2482: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2483: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2484: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2485: l_party_id := H_SCORES.FIRST;
2486: WHILE l_party_id IS NOT NULL LOOP
2487: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2488: l_party_id:=H_SCORES.NEXT(l_party_id);
2510: l_search_ctx_id,H_PARTY_ID(I));
2511: l_party_id_idx := H_PARTY_ID.COUNT+1;
2512: ELSE
2513: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2514: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2515: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2516: 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);
2517: END IF;
2518: IF p_index>1 THEN
2532: END IF;
2533: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2534: 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);
2535: END IF;
2536: 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);
2537: LOOP
2538: FETCH l_party_site_cur INTO
2539: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
2540: EXIT WHEN l_party_site_cur%NOTFOUND;
2588: END IF;
2589: END;
2590:
2591: /** Private procedure to acquire and score at contact point level ***/
2592: 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
2593: l_party_id_idx NUMBER:=1;
2594: l_ctx_id NUMBER;
2595: h_ct_id HZ_PARTY_SEARCH.IDList;
2596: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2622: l_search_ctx_id,H_PARTY_ID(I));
2623: l_party_id_idx := H_PARTY_ID.COUNT+1;
2624: ELSE
2625: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2626: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2627: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2628: 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);
2629: END IF;
2630: IF p_index>1 THEN
2644: END IF;
2645: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2646: 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);
2647: END IF;
2648: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2649: LOOP
2650: FETCH l_contact_cur INTO
2651: l_org_contact_id, l_ct_party_id ;
2652: EXIT WHEN l_contact_cur%NOTFOUND;
2697: END IF;
2698: END;
2699:
2700: /** Private procedure to acquire and score at contact point level ***/
2701: 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
2702: l_party_id_idx NUMBER:=1;
2703: l_ctx_id NUMBER;
2704: h_cpt_id HZ_PARTY_SEARCH.IDList;
2705: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2733: l_search_ctx_id,H_PARTY_ID(I));
2734: l_party_id_idx := H_PARTY_ID.COUNT+1;
2735: ELSE
2736: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2737: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2738: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2739: 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);
2740: END IF;
2741: IF p_index>1 THEN
2755: END IF;
2756: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2757: 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);
2758: END IF;
2759: 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);
2760: LOOP
2761: FETCH l_contact_pt_cur INTO
2762: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
2763: EXIT WHEN l_contact_pt_cur%NOTFOUND;
2855: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2856: l_threshold := 85;
2857: other_acq_criteria_exists := TRUE ;
2858: --check if acquisition criteria exists for any other entity
2859: IF l_party_contains_str IS NOT NULL THEN
2860: acq_cnt := 1;
2861: END IF;
2862: IF l_party_site_contains_str IS NOT NULL THEN
2863: acq_cnt := acq_cnt+1;
2858: --check if acquisition criteria exists for any other entity
2859: IF l_party_contains_str IS NOT NULL THEN
2860: acq_cnt := 1;
2861: END IF;
2862: IF l_party_site_contains_str IS NOT NULL THEN
2863: acq_cnt := acq_cnt+1;
2864: END IF;
2865: IF l_contact_contains_str IS NOT NULL THEN
2866: acq_cnt := acq_cnt+1;
2861: END IF;
2862: IF l_party_site_contains_str IS NOT NULL THEN
2863: acq_cnt := acq_cnt+1;
2864: END IF;
2865: IF l_contact_contains_str IS NOT NULL THEN
2866: acq_cnt := acq_cnt+1;
2867: END IF;
2868: IF l_contact_pt_contains_str IS NOT NULL THEN
2869: acq_cnt := acq_cnt+1;
2864: END IF;
2865: IF l_contact_contains_str IS NOT NULL THEN
2866: acq_cnt := acq_cnt+1;
2867: END IF;
2868: IF l_contact_pt_contains_str IS NOT NULL THEN
2869: acq_cnt := acq_cnt+1;
2870: END IF;
2871: IF acq_cnt>1 THEN
2872: other_acq_criteria_exists := TRUE;
2898: FND_MSG_PUB.ADD;
2899: RAISE FND_API.G_EXC_ERROR;
2900: END IF;
2901: END IF;
2902: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2903: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2904: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2905: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2906: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2899: RAISE FND_API.G_EXC_ERROR;
2900: END IF;
2901: END IF;
2902: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2903: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2904: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2905: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2906: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2907: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2900: END IF;
2901: END IF;
2902: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2903: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2904: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2905: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2906: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2907: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2908: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2901: END IF;
2902: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2903: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2904: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2905: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2906: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2907: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2908: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2909: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2902: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2903: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2904: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2905: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2906: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2907: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2908: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2909: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2910: END IF;
2903: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2904: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2905: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2906: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2907: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2908: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2909: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2910: END IF;
2911: END;
2904: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2905: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2906: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2907: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2908: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2909: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2910: END IF;
2911: END;
2912: /************ End of find_parties private procedures **********/
2905: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2906: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2907: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2908: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2909: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2910: END IF;
2911: END;
2912: /************ End of find_parties private procedures **********/
2913:
2988: 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);
2989: END IF;
2990: -- ************************************************************
2991: -- STEP 2. Setup of intermedia query strings for Acquisition query
2992: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2993: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2994: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2995: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2996: 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);
2989: END IF;
2990: -- ************************************************************
2991: -- STEP 2. Setup of intermedia query strings for Acquisition query
2992: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2993: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2994: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2995: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2996: 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);
2997: END IF;
2990: -- ************************************************************
2991: -- STEP 2. Setup of intermedia query strings for Acquisition query
2992: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2993: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2994: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2995: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2996: 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);
2997: END IF;
2998: l_denorm_max_score:=0;
3022: END IF;
3023: ELSE
3024: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3025: END IF;
3026: 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((85/145)*l_entered_max_score));
3027: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3028:
3029: -- Setup Search Context ID
3030: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3032:
3033: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3034: 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);
3035: END IF;
3036: IF l_party_contains_str IS NULL THEN
3037: defpt := 1;
3038: END IF;
3039: IF l_party_site_contains_str IS NULL THEN
3040: defps := 1;
3035: END IF;
3036: IF l_party_contains_str IS NULL THEN
3037: defpt := 1;
3038: END IF;
3039: IF l_party_site_contains_str IS NULL THEN
3040: defps := 1;
3041: END IF;
3042: IF l_contact_contains_str IS NULL THEN
3043: defct := 1;
3038: END IF;
3039: IF l_party_site_contains_str IS NULL THEN
3040: defps := 1;
3041: END IF;
3042: IF l_contact_contains_str IS NULL THEN
3043: defct := 1;
3044: END IF;
3045: IF l_contact_pt_contains_str IS NULL THEN
3046: defcpt := 1;
3041: END IF;
3042: IF l_contact_contains_str IS NULL THEN
3043: defct := 1;
3044: END IF;
3045: IF l_contact_pt_contains_str IS NULL THEN
3046: defcpt := 1;
3047: END IF;
3048:
3049: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3050: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3051: 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);
3052: END IF;
3053: 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);
3054: 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);
3055: 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);
3056: 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);
3057: 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);
3058: 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);
3051: 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);
3052: END IF;
3053: 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);
3054: 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);
3055: 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);
3056: 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);
3057: 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);
3058: 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);
3059: END IF;
3052: END IF;
3053: 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);
3054: 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);
3055: 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);
3056: 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);
3057: 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);
3058: 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);
3059: END IF;
3060: FOR I in 1..5 LOOP
3053: 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);
3054: 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);
3055: 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);
3056: 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);
3057: 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);
3058: 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);
3059: END IF;
3060: FOR I in 1..5 LOOP
3061: do_eval(I);
3167: x_num_matches OUT NUMBER
3168: ) IS
3169:
3170: -- Strings to hold the generated Intermedia query strings
3171: l_party_contains_str VARCHAR2(32000);
3172: l_party_site_contains_str VARCHAR2(32000);
3173: l_contact_contains_str VARCHAR2(32000);
3174: l_contact_pt_contains_str VARCHAR2(32000);
3175: l_denorm_str VARCHAR2(32000);
3168: ) IS
3169:
3170: -- Strings to hold the generated Intermedia query strings
3171: l_party_contains_str VARCHAR2(32000);
3172: l_party_site_contains_str VARCHAR2(32000);
3173: l_contact_contains_str VARCHAR2(32000);
3174: l_contact_pt_contains_str VARCHAR2(32000);
3175: l_denorm_str VARCHAR2(32000);
3176: l_ps_denorm_str VARCHAR2(32000);
3169:
3170: -- Strings to hold the generated Intermedia query strings
3171: l_party_contains_str VARCHAR2(32000);
3172: l_party_site_contains_str VARCHAR2(32000);
3173: l_contact_contains_str VARCHAR2(32000);
3174: l_contact_pt_contains_str VARCHAR2(32000);
3175: l_denorm_str VARCHAR2(32000);
3176: l_ps_denorm_str VARCHAR2(32000);
3177: l_ct_denorm_str VARCHAR2(32000);
3170: -- Strings to hold the generated Intermedia query strings
3171: l_party_contains_str VARCHAR2(32000);
3172: l_party_site_contains_str VARCHAR2(32000);
3173: l_contact_contains_str VARCHAR2(32000);
3174: l_contact_pt_contains_str VARCHAR2(32000);
3175: l_denorm_str VARCHAR2(32000);
3176: l_ps_denorm_str VARCHAR2(32000);
3177: l_ct_denorm_str VARCHAR2(32000);
3178: l_cpt_denorm_str VARCHAR2(32000);
3297: call_type(2) := 'OR';
3298: END;
3299:
3300: /** Private procedure to acquire and score at party level ***/
3301: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3302: l_party_id_idx NUMBER:=1;
3303: l_ctx_id NUMBER;
3304: l_TX35_new varchar2(4000);
3305: BEGIN
3319: l_search_ctx_id,H_PARTY_ID(I));
3320: l_party_id_idx := H_PARTY_ID.COUNT+1;
3321: ELSE
3322: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3323: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3324: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3325: 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);
3326: END IF;
3327: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3327: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3328: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3329: 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);
3330: END IF;
3331: IF (l_party_site_contains_str IS NULL AND
3332: l_contact_contains_str IS NULL AND
3333: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3334: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3335: RAISE L_RETURN_IMM_EXC;
3328: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3329: 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);
3330: END IF;
3331: IF (l_party_site_contains_str IS NULL AND
3332: l_contact_contains_str IS NULL AND
3333: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3334: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3335: RAISE L_RETURN_IMM_EXC;
3336: ELSE
3329: 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);
3330: END IF;
3331: IF (l_party_site_contains_str IS NULL AND
3332: l_contact_contains_str IS NULL AND
3333: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3334: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3335: RAISE L_RETURN_IMM_EXC;
3336: ELSE
3337: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3333: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3334: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3335: RAISE L_RETURN_IMM_EXC;
3336: ELSE
3337: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3338: END IF;
3339: ELSE
3340: IF p_index>1 THEN
3341: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3353: l_ctx_id := NULL;
3354: l_party_id_idx := H_PARTY_ID.COUNT+1;
3355: END IF;
3356: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3357: 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);
3358: END IF;
3359: LOOP
3360: FETCH l_party_cur INTO
3361: l_party_id ;
3375: l_party_id_idx:= l_party_id_idx+1;
3376: END IF;
3377: IF (l_party_id_idx-1)>l_max_thresh THEN
3378: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3379: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3380: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3381: AND l_contact_pt_contains_str IS NULL THEN
3382: H_PARTY_ID.DELETE(l_party_id_idx-1);
3383: H_PARTY_ID_LIST.DELETE(l_index);
3377: IF (l_party_id_idx-1)>l_max_thresh THEN
3378: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3379: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3380: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3381: AND l_contact_pt_contains_str IS NULL THEN
3382: H_PARTY_ID.DELETE(l_party_id_idx-1);
3383: H_PARTY_ID_LIST.DELETE(l_index);
3384: H_SCORES.DELETE(l_index);
3385: EXIT;
3406: CLOSE l_party_cur;
3407: ROLLBACK to eval_start;
3408: END;
3409: PROCEDURE open_person_contact_cursor(
3410: p_contains_str VARCHAR2,
3411: p_search_ctx_id NUMBER,
3412: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3413: BEGIN
3414: OPEN x_cursor FOR
3413: BEGIN
3414: OPEN x_cursor FOR
3415: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3416: FROM HZ_STAGED_CONTACTS stage
3417: WHERE contains( concat_col, p_contains_str)>0
3418: AND ORG_CONTACT_ID in (
3419: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3420: org_contact_id
3421: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
3437: end if;
3438: END;
3439:
3440: /** Private procedure to acquire and score at party site level ***/
3441: 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
3442: l_party_id_idx NUMBER:=1;
3443: l_ctx_id NUMBER;
3444: h_ps_id HZ_PARTY_SEARCH.IDList;
3445: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3465: ELSE
3466: l_party_id_idx := H_PARTY_ID.COUNT+1;
3467: l_ctx_id := NULL;
3468: END IF;
3469: 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);
3470: LOOP
3471: FETCH l_party_site_cur INTO
3472: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
3473: EXIT WHEN l_party_site_cur%NOTFOUND;
3536: END IF;
3537: END;
3538:
3539: /** Private procedure to acquire and score at party site level ***/
3540: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3541: l_party_id_idx NUMBER:=1;
3542: l_ctx_id NUMBER;
3543: h_ct_id HZ_PARTY_SEARCH.IDList;
3544: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3552: FORALL I in 1..H_PARTY_ID.COUNT
3553: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3554: l_search_ctx_id,H_PARTY_ID(I));
3555: l_party_id_idx := H_PARTY_ID.COUNT+1;
3556: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3557: LOOP
3558: FETCH l_contact_cur INTO
3559: l_org_contact_id, l_ct_party_id ;
3560: EXIT WHEN l_contact_cur%NOTFOUND;
3594: END IF;
3595: END;
3596:
3597: /** Private procedure to acquire and score at contact point level ***/
3598: 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
3599: l_party_id_idx NUMBER:=1;
3600: l_ctx_id NUMBER;
3601: h_cpt_id HZ_PARTY_SEARCH.IDList;
3602: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3639: END IF;
3640: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3641: 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);
3642: END IF;
3643: 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);
3644: LOOP
3645: FETCH l_contact_pt_cur INTO
3646: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3647: EXIT WHEN l_contact_pt_cur%NOTFOUND;
3747: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3748: l_threshold := 85;
3749: other_acq_criteria_exists := TRUE ;
3750: --check if acquisition criteria exists for any other entity
3751: IF l_party_contains_str IS NOT NULL THEN
3752: acq_cnt := 1;
3753: END IF;
3754: IF l_party_site_contains_str IS NOT NULL THEN
3755: acq_cnt := acq_cnt+1;
3750: --check if acquisition criteria exists for any other entity
3751: IF l_party_contains_str IS NOT NULL THEN
3752: acq_cnt := 1;
3753: END IF;
3754: IF l_party_site_contains_str IS NOT NULL THEN
3755: acq_cnt := acq_cnt+1;
3756: END IF;
3757: IF l_contact_contains_str IS NOT NULL THEN
3758: acq_cnt := acq_cnt+1;
3753: END IF;
3754: IF l_party_site_contains_str IS NOT NULL THEN
3755: acq_cnt := acq_cnt+1;
3756: END IF;
3757: IF l_contact_contains_str IS NOT NULL THEN
3758: acq_cnt := acq_cnt+1;
3759: END IF;
3760: IF l_contact_pt_contains_str IS NOT NULL THEN
3761: acq_cnt := acq_cnt+1;
3756: END IF;
3757: IF l_contact_contains_str IS NOT NULL THEN
3758: acq_cnt := acq_cnt+1;
3759: END IF;
3760: IF l_contact_pt_contains_str IS NOT NULL THEN
3761: acq_cnt := acq_cnt+1;
3762: END IF;
3763: IF acq_cnt>1 THEN
3764: other_acq_criteria_exists := TRUE;
3790: FND_MSG_PUB.ADD;
3791: RAISE FND_API.G_EXC_ERROR;
3792: END IF;
3793: END IF;
3794: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3795: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3796: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3797: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3798: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3791: RAISE FND_API.G_EXC_ERROR;
3792: END IF;
3793: END IF;
3794: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3795: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3796: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3797: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3798: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3799: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3792: END IF;
3793: END IF;
3794: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3795: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3796: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3797: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3798: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3799: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3800: END IF;
3793: END IF;
3794: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3795: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3796: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3797: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3798: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3799: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3800: END IF;
3801: END;
3794: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3795: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3796: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3797: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3798: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3799: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3800: END IF;
3801: END;
3802: /************ End of find_persons private procedures **********/
3795: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3796: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3797: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3798: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3799: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3800: END IF;
3801: END;
3802: /************ End of find_persons private procedures **********/
3803:
3830: g_score_until_thresh := false;
3831: END IF;
3832: -- ************************************************************
3833: -- STEP 2. Setup of intermedia query strings for Acquisition query
3834: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3835: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3836: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3837: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3838: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3831: END IF;
3832: -- ************************************************************
3833: -- STEP 2. Setup of intermedia query strings for Acquisition query
3834: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3835: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3836: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3837: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3838: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3839:
3832: -- ************************************************************
3833: -- STEP 2. Setup of intermedia query strings for Acquisition query
3834: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3835: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3836: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3837: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3838: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3839:
3840: -- Setup Search Context ID
3833: -- STEP 2. Setup of intermedia query strings for Acquisition query
3834: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3835: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3836: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3837: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3838: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3839:
3840: -- Setup Search Context ID
3841: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3840: -- Setup Search Context ID
3841: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3842: x_search_ctx_id := l_search_ctx_id;
3843:
3844: IF l_party_contains_str IS NULL THEN
3845: defpt := 1;
3846: END IF;
3847: IF l_party_site_contains_str IS NULL THEN
3848: defps := 1;
3843:
3844: IF l_party_contains_str IS NULL THEN
3845: defpt := 1;
3846: END IF;
3847: IF l_party_site_contains_str IS NULL THEN
3848: defps := 1;
3849: END IF;
3850: IF l_contact_contains_str IS NULL THEN
3851: defct := 1;
3846: END IF;
3847: IF l_party_site_contains_str IS NULL THEN
3848: defps := 1;
3849: END IF;
3850: IF l_contact_contains_str IS NULL THEN
3851: defct := 1;
3852: END IF;
3853: IF l_contact_pt_contains_str IS NULL THEN
3854: defcpt := 1;
3849: END IF;
3850: IF l_contact_contains_str IS NULL THEN
3851: defct := 1;
3852: END IF;
3853: IF l_contact_pt_contains_str IS NULL THEN
3854: defcpt := 1;
3855: END IF;
3856:
3857: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3855: END IF;
3856:
3857: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3858: 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);
3859: 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);
3860: 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);
3861: 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);
3862: 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);
3863: 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);
3856:
3857: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3858: 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);
3859: 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);
3860: 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);
3861: 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);
3862: 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);
3863: 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);
3864: END IF;
3857: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3858: 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);
3859: 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);
3860: 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);
3861: 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);
3862: 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);
3863: 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);
3864: END IF;
3865: FOR I in 1..5 LOOP
3858: 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);
3859: 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);
3860: 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);
3861: 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);
3862: 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);
3863: 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);
3864: END IF;
3865: FOR I in 1..5 LOOP
3866: do_eval(I);
3864: END IF;
3865: FOR I in 1..5 LOOP
3866: do_eval(I);
3867: END LOOP;
3868: IF l_contact_contains_str IS NOT NULL THEN
3869: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3870: END IF;
3871: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3872: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 59 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3865: FOR I in 1..5 LOOP
3866: do_eval(I);
3867: END LOOP;
3868: IF l_contact_contains_str IS NOT NULL THEN
3869: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3870: END IF;
3871: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3872: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 59 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3873: END IF;
4666: x_num_matches OUT NUMBER
4667: ) IS
4668:
4669: -- Strings to hold the generated Intermedia query strings
4670: l_party_contains_str VARCHAR2(32000);
4671: l_match_idx NUMBER;
4672: l_party_site_contains_str VARCHAR2(32000);
4673: l_contact_contains_str VARCHAR2(32000);
4674: l_contact_pt_contains_str VARCHAR2(32000);
4668:
4669: -- Strings to hold the generated Intermedia query strings
4670: l_party_contains_str VARCHAR2(32000);
4671: l_match_idx NUMBER;
4672: l_party_site_contains_str VARCHAR2(32000);
4673: l_contact_contains_str VARCHAR2(32000);
4674: l_contact_pt_contains_str VARCHAR2(32000);
4675: l_tmp VARCHAR2(32000);
4676:
4669: -- Strings to hold the generated Intermedia query strings
4670: l_party_contains_str VARCHAR2(32000);
4671: l_match_idx NUMBER;
4672: l_party_site_contains_str VARCHAR2(32000);
4673: l_contact_contains_str VARCHAR2(32000);
4674: l_contact_pt_contains_str VARCHAR2(32000);
4675: l_tmp VARCHAR2(32000);
4676:
4677: -- Other local variables
4670: l_party_contains_str VARCHAR2(32000);
4671: l_match_idx NUMBER;
4672: l_party_site_contains_str VARCHAR2(32000);
4673: l_contact_contains_str VARCHAR2(32000);
4674: l_contact_pt_contains_str VARCHAR2(32000);
4675: l_tmp VARCHAR2(32000);
4676:
4677: -- Other local variables
4678: l_match_str VARCHAR2(30); -- Match type (AND or OR)
4755: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4756:
4757: -- ************************************************************
4758: -- STEP 2. Setup of intermedia query strings for Acquisition query
4759: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4760: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4761: 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);
4762:
4763: -- Setup Search Context ID
4756:
4757: -- ************************************************************
4758: -- STEP 2. Setup of intermedia query strings for Acquisition query
4759: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4760: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4761: 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);
4762:
4763: -- Setup Search Context ID
4764: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4765: x_search_ctx_id := l_search_ctx_id;
4766:
4767: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4768: 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);
4769: 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);
4770: 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);
4771: 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);
4772: END IF;
4773: IF l_party_site_contains_str IS NULL THEN
4766:
4767: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4768: 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);
4769: 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);
4770: 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);
4771: 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);
4772: END IF;
4773: IF l_party_site_contains_str IS NULL THEN
4774: defps := 1;
4769: 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);
4770: 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);
4771: 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);
4772: END IF;
4773: IF l_party_site_contains_str IS NULL THEN
4774: defps := 1;
4775: END IF;
4776: IF l_contact_pt_contains_str IS NULL THEN
4777: defcpt := 1;
4772: END IF;
4773: IF l_party_site_contains_str IS NULL THEN
4774: defps := 1;
4775: END IF;
4776: IF l_contact_pt_contains_str IS NULL THEN
4777: defcpt := 1;
4778: END IF;
4779:
4780: IF l_party_site_contains_str IS NOT NULL THEN
4776: IF l_contact_pt_contains_str IS NULL THEN
4777: defcpt := 1;
4778: END IF;
4779:
4780: IF l_party_site_contains_str IS NOT NULL THEN
4781: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4782: LOOP
4783: FETCH l_party_site_cur INTO
4784: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
4777: defcpt := 1;
4778: END IF;
4779:
4780: IF l_party_site_contains_str IS NOT NULL THEN
4781: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4782: LOOP
4783: FETCH l_party_site_cur INTO
4784: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
4785: EXIT WHEN l_party_site_cur%NOTFOUND;
4800: END LOOP;
4801: CLOSE l_party_site_cur;
4802: END IF;
4803:
4804: IF l_contact_pt_contains_str IS NOT NULL THEN
4805: SAVEPOINT eval_start;
4806: IF l_match_str = ' AND ' OR (
4807: ((l_cpt_max_score/l_entered_max_score)<(85/145))
4808: ) THEN
4808: ) THEN
4809: FORALL I in 1..l_match_ps_list.COUNT
4810: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4811: values (l_search_ctx_id,l_match_ps_list(I));
4812: 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');
4813: ELSE
4814: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4815: END IF;
4816: LOOP
4810: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4811: values (l_search_ctx_id,l_match_ps_list(I));
4812: 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');
4813: ELSE
4814: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4815: END IF;
4816: LOOP
4817: FETCH l_contact_pt_cur INTO
4818: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
4910: x_num_matches OUT NUMBER
4911: ) IS
4912:
4913: -- Strings to hold the generated Intermedia query strings
4914: l_party_contains_str VARCHAR2(32000);
4915: l_party_site_contains_str VARCHAR2(32000);
4916: l_contact_contains_str VARCHAR2(32000);
4917: l_contact_pt_contains_str VARCHAR2(32000);
4918: l_tmp VARCHAR2(32000);
4911: ) IS
4912:
4913: -- Strings to hold the generated Intermedia query strings
4914: l_party_contains_str VARCHAR2(32000);
4915: l_party_site_contains_str VARCHAR2(32000);
4916: l_contact_contains_str VARCHAR2(32000);
4917: l_contact_pt_contains_str VARCHAR2(32000);
4918: l_tmp VARCHAR2(32000);
4919:
4912:
4913: -- Strings to hold the generated Intermedia query strings
4914: l_party_contains_str VARCHAR2(32000);
4915: l_party_site_contains_str VARCHAR2(32000);
4916: l_contact_contains_str VARCHAR2(32000);
4917: l_contact_pt_contains_str VARCHAR2(32000);
4918: l_tmp VARCHAR2(32000);
4919:
4920: -- Other local variables
4913: -- Strings to hold the generated Intermedia query strings
4914: l_party_contains_str VARCHAR2(32000);
4915: l_party_site_contains_str VARCHAR2(32000);
4916: l_contact_contains_str VARCHAR2(32000);
4917: l_contact_pt_contains_str VARCHAR2(32000);
4918: l_tmp VARCHAR2(32000);
4919:
4920: -- Other local variables
4921: l_match_str VARCHAR2(30); -- Match type (AND or OR)
4984: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4985:
4986: -- ************************************************************
4987: -- STEP 2. Setup of intermedia query strings for Acquisition query
4988: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4989: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4990: 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);
4991:
4992: -- Setup Search Context ID
4985:
4986: -- ************************************************************
4987: -- STEP 2. Setup of intermedia query strings for Acquisition query
4988: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4989: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4990: 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);
4991:
4992: -- Setup Search Context ID
4993: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4994: x_search_ctx_id := l_search_ctx_id;
4995:
4996: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4997: 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);
4998: 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);
4999: 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);
5000: 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);
5001: END IF;
5002: IF l_contact_contains_str IS NULL THEN
4995:
4996: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4997: 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);
4998: 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);
4999: 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);
5000: 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);
5001: END IF;
5002: IF l_contact_contains_str IS NULL THEN
5003: defct := 1;
4998: 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);
4999: 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);
5000: 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);
5001: END IF;
5002: IF l_contact_contains_str IS NULL THEN
5003: defct := 1;
5004: END IF;
5005: IF l_contact_pt_contains_str IS NULL THEN
5006: defcpt := 1;
5001: END IF;
5002: IF l_contact_contains_str IS NULL THEN
5003: defct := 1;
5004: END IF;
5005: IF l_contact_pt_contains_str IS NULL THEN
5006: defcpt := 1;
5007: END IF;
5008:
5009: IF l_contact_contains_str IS NOT NULL THEN
5005: IF l_contact_pt_contains_str IS NULL THEN
5006: defcpt := 1;
5007: END IF;
5008:
5009: IF l_contact_contains_str IS NOT NULL THEN
5010: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5011: LOOP
5012: FETCH l_contact_cur INTO
5013: l_org_contact_id, l_ct_party_id ;
5006: defcpt := 1;
5007: END IF;
5008:
5009: IF l_contact_contains_str IS NOT NULL THEN
5010: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5011: LOOP
5012: FETCH l_contact_cur INTO
5013: l_org_contact_id, l_ct_party_id ;
5014: EXIT WHEN l_contact_cur%NOTFOUND;
5027: END LOOP;
5028: CLOSE l_contact_cur;
5029: END IF;
5030:
5031: IF l_contact_pt_contains_str IS NOT NULL THEN
5032: SAVEPOINT eval_start;
5033: IF l_match_str = ' AND ' OR (
5034: ((l_cpt_max_score/l_entered_max_score)<(85/145))
5035: ) THEN
5035: ) THEN
5036: FORALL I in 1..l_match_contact_list.COUNT
5037: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5038: values (l_search_ctx_id,l_match_contact_list(I));
5039: 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');
5040: ELSE
5041: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5042: END IF;
5043: LOOP
5037: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5038: values (l_search_ctx_id,l_match_contact_list(I));
5039: 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');
5040: ELSE
5041: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5042: END IF;
5043: LOOP
5044: FETCH l_contact_pt_cur INTO
5045: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
5135: ) IS
5136:
5137:
5138: -- Strings to hold the generated Intermedia query strings
5139: l_contact_pt_contains_str VARCHAR2(32000);
5140: l_tmp VARCHAR2(32000);
5141:
5142: -- Other local variables
5143: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5193: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5194:
5195: -- ************************************************************
5196: -- STEP 2. Setup of intermedia query strings for Acquisition query
5197: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5198: 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);
5199:
5200: -- Setup Search Context ID
5201: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5201: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5202: x_search_ctx_id := l_search_ctx_id;
5203: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5204: 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);
5205: 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);
5206: 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);
5207: END IF;
5208:
5209: IF l_contact_pt_contains_str IS NOT NULL THEN
5205: 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);
5206: 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);
5207: END IF;
5208:
5209: IF l_contact_pt_contains_str IS NOT NULL THEN
5210: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5211: cnt := 1;
5212: LOOP
5213: FETCH l_contact_pt_cur INTO
5206: 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);
5207: END IF;
5208:
5209: IF l_contact_pt_contains_str IS NOT NULL THEN
5210: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5211: cnt := 1;
5212: LOOP
5213: FETCH l_contact_pt_cur INTO
5214: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
5277: x_search_ctx_id IN OUT NUMBER
5278: ) IS
5279:
5280: -- Strings to hold the generated Intermedia query strings
5281: l_party_contains_str VARCHAR2(32000);
5282: l_party_site_contains_str VARCHAR2(32000);
5283: l_contact_contains_str VARCHAR2(32000);
5284: l_contact_pt_contains_str VARCHAR2(32000);
5285: l_tmp VARCHAR2(32000);
5278: ) IS
5279:
5280: -- Strings to hold the generated Intermedia query strings
5281: l_party_contains_str VARCHAR2(32000);
5282: l_party_site_contains_str VARCHAR2(32000);
5283: l_contact_contains_str VARCHAR2(32000);
5284: l_contact_pt_contains_str VARCHAR2(32000);
5285: l_tmp VARCHAR2(32000);
5286:
5279:
5280: -- Strings to hold the generated Intermedia query strings
5281: l_party_contains_str VARCHAR2(32000);
5282: l_party_site_contains_str VARCHAR2(32000);
5283: l_contact_contains_str VARCHAR2(32000);
5284: l_contact_pt_contains_str VARCHAR2(32000);
5285: l_tmp VARCHAR2(32000);
5286:
5287: -- Other local variables
5280: -- Strings to hold the generated Intermedia query strings
5281: l_party_contains_str VARCHAR2(32000);
5282: l_party_site_contains_str VARCHAR2(32000);
5283: l_contact_contains_str VARCHAR2(32000);
5284: l_contact_pt_contains_str VARCHAR2(32000);
5285: l_tmp VARCHAR2(32000);
5286:
5287: -- Other local variables
5288: l_match_str VARCHAR2(30); -- Match type (AND or OR)
5362: g_score_until_thresh := false;
5363: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5364: -- ************************************************************
5365: -- STEP 2. Setup of intermedia query strings for Acquisition query
5366: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5367: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5368: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5369: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5370:
5363: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5364: -- ************************************************************
5365: -- STEP 2. Setup of intermedia query strings for Acquisition query
5366: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5367: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5368: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5369: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5370:
5371: -- Setup Search Context ID
5364: -- ************************************************************
5365: -- STEP 2. Setup of intermedia query strings for Acquisition query
5366: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5367: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5368: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5369: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5370:
5371: -- Setup Search Context ID
5372: IF x_search_ctx_id IS NULL THEN
5383: EXIT WHEN l_party_cur%NOTFOUND;
5384: END LOOP;
5385: CLOSE l_party_cur;
5386:
5387: IF l_party_site_contains_str IS NOT NULL THEN
5388: l_max_score := 0;
5389: l_max_id := 0;
5390: l_max_idx := 0;
5391: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5388: l_max_score := 0;
5389: l_max_id := 0;
5390: l_max_idx := 0;
5391: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5392: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5393: ELSE
5394: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5395: END IF;
5396: LOOP
5390: l_max_idx := 0;
5391: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5392: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5393: ELSE
5394: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5395: END IF;
5396: LOOP
5397: FETCH l_party_site_cur INTO
5398: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX11, l_TX12, l_TX13, l_TX14, l_TX15, l_TX20, l_TX21, l_TX22;
5421: 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_TX9,l_max_TX10,l_max_TX11,l_max_TX12,l_max_TX13,l_max_TX14,l_max_TX15,l_max_TX20,l_max_TX21,l_max_TX22,l_max_idx);
5422: END IF;
5423: END IF;
5424:
5425: IF l_contact_contains_str IS NOT NULL THEN
5426: l_max_score := 0;
5427: l_max_id := 0;
5428: l_max_idx := 0;
5429: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5425: IF l_contact_contains_str IS NOT NULL THEN
5426: l_max_score := 0;
5427: l_max_id := 0;
5428: l_max_idx := 0;
5429: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5430: LOOP
5431: FETCH l_contact_cur INTO
5432: l_org_contact_id, l_ct_party_id ;
5433: EXIT WHEN l_contact_cur%NOTFOUND;
5443: 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);
5444: END IF;
5445: END IF;
5446:
5447: IF l_contact_pt_contains_str IS NOT NULL THEN
5448: l_max_score := 0;
5449: l_max_id := 0;
5450: l_max_idx := 0;
5451: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5448: l_max_score := 0;
5449: l_max_id := 0;
5450: l_max_idx := 0;
5451: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5452: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5453: ELSE
5454: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5455: END IF;
5456: LOOP
5450: l_max_idx := 0;
5451: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5452: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5453: ELSE
5454: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5455: END IF;
5456: LOOP
5457: FETCH l_contact_pt_cur INTO
5458: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;