eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_MATCH_RULE_35 dependencies on CONTAINS

Line 70: p_contains_str VARCHAR2) RETURN NUMBER IS

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

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

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

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

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

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

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

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

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

Line 1082: l_party_contains_str VARCHAR2(32000);

1078: p_party_max_score NUMBER,
1079: p_denorm_max_score NUMBER,
1080: p_non_denorm_max_score NUMBER,
1081: p_threshold NUMBER) RETURN VARCHAR2 IS
1082: l_party_contains_str VARCHAR2(32000);
1083: l_party_filter VARCHAR2(1) := null;
1084: l_prim_temp VARCHAR2(4000);
1085: l_denorm_str VARCHAR2(4000);
1086: BEGIN

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

1122:
1123: IF l_denorm_str IS NOT NULL THEN
1124: IF (p_non_denorm_max_score+10)>=p_threshold THEN
1125: l_denorm_str := NULL;
1126: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1127: ELSIF (p_non_denorm_max_score+p_denorm_max_score+10)>=p_threshold THEN
1128: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1129: l_denorm_str := NULL;
1130: END IF;

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

1124: IF (p_non_denorm_max_score+10)>=p_threshold THEN
1125: l_denorm_str := NULL;
1126: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1127: ELSIF (p_non_denorm_max_score+p_denorm_max_score+10)>=p_threshold THEN
1128: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1129: l_denorm_str := NULL;
1130: END IF;
1131: ELSE
1132: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1128: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1129: l_denorm_str := NULL;
1130: END IF;
1131: ELSE
1132: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1133: END IF;
1134:
1135: -- Setup query string for SIC_CODE
1136: l_prim_temp := null;

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

1139:
1140: IF l_denorm_str IS NOT NULL THEN
1141: IF (p_non_denorm_max_score+20)>=p_threshold THEN
1142: l_denorm_str := NULL;
1143: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1144: ELSIF (p_non_denorm_max_score+p_denorm_max_score+20)>=p_threshold THEN
1145: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1146: l_denorm_str := NULL;
1147: END IF;

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

1141: IF (p_non_denorm_max_score+20)>=p_threshold THEN
1142: l_denorm_str := NULL;
1143: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1144: ELSIF (p_non_denorm_max_score+p_denorm_max_score+20)>=p_threshold THEN
1145: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1146: l_denorm_str := NULL;
1147: END IF;
1148: ELSE
1149: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1145: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1146: l_denorm_str := NULL;
1147: END IF;
1148: ELSE
1149: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1150: END IF;
1151:
1152: -- Setup query string for PARTY_ALL_NAMES
1153: l_prim_temp := null;

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

1158:
1159: IF l_denorm_str IS NOT NULL THEN
1160: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1161: l_denorm_str := NULL;
1162: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1163: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1164: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1165: l_denorm_str := NULL;
1166: END IF;

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

1160: IF (p_non_denorm_max_score+40)>=p_threshold THEN
1161: l_denorm_str := NULL;
1162: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1163: ELSIF (p_non_denorm_max_score+p_denorm_max_score+40)>=p_threshold THEN
1164: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1165: l_denorm_str := NULL;
1166: END IF;
1167: ELSE
1168: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1164: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1165: l_denorm_str := NULL;
1166: END IF;
1167: ELSE
1168: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1169: END IF;
1170:
1171: -- Setup query string for PARTY_NUMBER
1172: l_prim_temp := null;

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

1175:
1176: IF l_denorm_str IS NOT NULL THEN
1177: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1178: l_denorm_str := NULL;
1179: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1180: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1181: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1182: l_denorm_str := NULL;
1183: END IF;

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

1177: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1178: l_denorm_str := NULL;
1179: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1180: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1181: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1182: l_denorm_str := NULL;
1183: END IF;
1184: ELSE
1185: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1181: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1182: l_denorm_str := NULL;
1183: END IF;
1184: ELSE
1185: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1186: END IF;
1187: IF lengthb(l_party_contains_str) > 4000 THEN
1188: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1189: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 1187: IF lengthb(l_party_contains_str) > 4000 THEN

1183: END IF;
1184: ELSE
1185: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1186: END IF;
1187: IF lengthb(l_party_contains_str) > 4000 THEN
1188: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1189: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1190: FND_MSG_PUB.ADD;
1191: RAISE FND_API.G_EXC_ERROR;

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

1189: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1190: FND_MSG_PUB.ADD;
1191: RAISE FND_API.G_EXC_ERROR;
1192: END IF;
1193: IF (p_match_str = ' AND ' OR p_party_max_score 1194: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1195: 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);
1196: END IF;
1197: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

1191: RAISE FND_API.G_EXC_ERROR;
1192: END IF;
1193: IF (p_match_str = ' AND ' OR p_party_max_score 1194: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1195: 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);
1196: END IF;
1197: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1198: 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);
1199: END IF;

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

1194: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1195: 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);
1196: END IF;
1197: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1198: 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);
1199: END IF;
1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: 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);
1202: END IF;

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

1199: END IF;
1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: 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);
1202: END IF;
1203: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1204: ELSE
1205: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1206: 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);
1207: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

1202: END IF;
1203: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1204: ELSE
1205: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1206: 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);
1207: 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);
1208: END IF;
1209: RETURN l_party_contains_str;
1210: END IF;

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

1203: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1204: ELSE
1205: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1206: 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);
1207: 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);
1208: END IF;
1209: RETURN l_party_contains_str;
1210: END IF;
1211: END;

Line 1209: RETURN l_party_contains_str;

1205: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1206: 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);
1207: 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);
1208: END IF;
1209: RETURN l_party_contains_str;
1210: END IF;
1211: END;
1212: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1213: l_contains_str VARCHAR2(32000);

Line 1213: l_contains_str VARCHAR2(32000);

1209: RETURN l_party_contains_str;
1210: END IF;
1211: END;
1212: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1213: l_contains_str VARCHAR2(32000);
1214: l_contains_str_temp VARCHAR2(32000);
1215: l_den_contains_str VARCHAR2(32000);
1216: l_den_contains_str_temp VARCHAR2(32000);
1217: l_filter_str VARCHAR2(4000) := null;

Line 1214: l_contains_str_temp VARCHAR2(32000);

1210: END IF;
1211: END;
1212: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1213: l_contains_str VARCHAR2(32000);
1214: l_contains_str_temp VARCHAR2(32000);
1215: l_den_contains_str VARCHAR2(32000);
1216: l_den_contains_str_temp VARCHAR2(32000);
1217: l_filter_str VARCHAR2(4000) := null;
1218: l_prim_temp VARCHAR2(4000) := null;

Line 1215: l_den_contains_str VARCHAR2(32000);

1211: END;
1212: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1213: l_contains_str VARCHAR2(32000);
1214: l_contains_str_temp VARCHAR2(32000);
1215: l_den_contains_str VARCHAR2(32000);
1216: l_den_contains_str_temp VARCHAR2(32000);
1217: l_filter_str VARCHAR2(4000) := null;
1218: l_prim_temp VARCHAR2(4000) := null;
1219: l_prim_temp_den VARCHAR2(4000) := null;

Line 1216: l_den_contains_str_temp VARCHAR2(32000);

1212: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1213: l_contains_str VARCHAR2(32000);
1214: l_contains_str_temp VARCHAR2(32000);
1215: l_den_contains_str VARCHAR2(32000);
1216: l_den_contains_str_temp VARCHAR2(32000);
1217: l_filter_str VARCHAR2(4000) := null;
1218: l_prim_temp VARCHAR2(4000) := null;
1219: l_prim_temp_den VARCHAR2(4000) := null;
1220: BEGIN

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

1222: 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);
1223: END IF;
1224: x_denorm_str := NULL;
1225:
1226: -- Dynamic setup of party site contains str
1227: --
1228: -- For each primary transformation add to intermedia query if it
1229: -- is not null
1230: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 1231: l_contains_str_temp := null;

1227: --
1228: -- For each primary transformation add to intermedia query if it
1229: -- is not null
1230: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1231: l_contains_str_temp := null;
1232: l_den_contains_str_temp := null;
1233: l_filter_str := null;
1234:
1235: -- Setup query string for ADDRESS

Line 1232: l_den_contains_str_temp := null;

1228: -- For each primary transformation add to intermedia query if it
1229: -- is not null
1230: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1231: l_contains_str_temp := null;
1232: l_den_contains_str_temp := null;
1233: l_filter_str := null;
1234:
1235: -- Setup query string for ADDRESS
1236: l_prim_temp := null;

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

1236: l_prim_temp := null;
1237: l_prim_temp_den := null;
1238: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1239: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1240: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1241:
1242: -- Setup query string for CITY
1243: l_prim_temp := null;
1244: l_prim_temp_den := null;

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

1245: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1246: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1247: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1248: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1249: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1250: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1251:
1252: -- Setup query string for STATE
1253: l_prim_temp := null;

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

1246: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1247: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1248: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1249: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1250: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1251:
1252: -- Setup query string for STATE
1253: l_prim_temp := null;
1254: l_prim_temp_den := null;

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

1255: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1256: g_party_site_stage_list(I).TX14,'A32',l_prim_temp);
1257: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1258: g_party_site_stage_list(I).TX14,'D_PS',l_prim_temp_den);
1259: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1260: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1261:
1262: -- Setup query string for COUNTRY
1263: l_prim_temp := null;

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

1256: g_party_site_stage_list(I).TX14,'A32',l_prim_temp);
1257: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1258: g_party_site_stage_list(I).TX14,'D_PS',l_prim_temp_den);
1259: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1260: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1261:
1262: -- Setup query string for COUNTRY
1263: l_prim_temp := null;
1264: l_prim_temp_den := null;

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

1265: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1266: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1267: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1268: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1269: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1270: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1271:
1272: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1273: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

1266: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1267: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1268: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1269: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1270: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1271:
1272: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1273: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1274: END LOOP;

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

1268: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1269: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1270: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1271:
1272: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1273: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1274: END LOOP;
1275: -- Add the search criteria to query string
1276: IF lengthb(l_contains_str) > 4000 THEN

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

1269: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1270: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1271:
1272: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1273: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1274: END LOOP;
1275: -- Add the search criteria to query string
1276: IF lengthb(l_contains_str) > 4000 THEN
1277: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1276: IF lengthb(l_contains_str) > 4000 THEN

1272: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1273: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1274: END LOOP;
1275: -- Add the search criteria to query string
1276: IF lengthb(l_contains_str) > 4000 THEN
1277: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1278: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1279: FND_MSG_PUB.ADD;
1280: RAISE FND_API.G_EXC_ERROR;

Line 1282: x_denorm_str := l_den_contains_str;

1278: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1279: FND_MSG_PUB.ADD;
1280: RAISE FND_API.G_EXC_ERROR;
1281: END IF;
1282: x_denorm_str := l_den_contains_str;
1283: RETURN l_contains_str;
1284: END;
1285:
1286: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1283: RETURN l_contains_str;

1279: FND_MSG_PUB.ADD;
1280: RAISE FND_API.G_EXC_ERROR;
1281: END IF;
1282: x_denorm_str := l_den_contains_str;
1283: RETURN l_contains_str;
1284: END;
1285:
1286: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1287: l_contains_str VARCHAR2(32000);

Line 1287: l_contains_str VARCHAR2(32000);

1283: RETURN l_contains_str;
1284: END;
1285:
1286: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1287: l_contains_str VARCHAR2(32000);
1288: l_contains_str_temp VARCHAR2(32000);
1289: l_den_contains_str VARCHAR2(32000);
1290: l_den_contains_str_temp VARCHAR2(32000);
1291: l_filter_str VARCHAR2(4000) := null;

Line 1288: l_contains_str_temp VARCHAR2(32000);

1284: END;
1285:
1286: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1287: l_contains_str VARCHAR2(32000);
1288: l_contains_str_temp VARCHAR2(32000);
1289: l_den_contains_str VARCHAR2(32000);
1290: l_den_contains_str_temp VARCHAR2(32000);
1291: l_filter_str VARCHAR2(4000) := null;
1292: l_prim_temp VARCHAR2(4000) := null;

Line 1289: l_den_contains_str VARCHAR2(32000);

1285:
1286: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1287: l_contains_str VARCHAR2(32000);
1288: l_contains_str_temp VARCHAR2(32000);
1289: l_den_contains_str VARCHAR2(32000);
1290: l_den_contains_str_temp VARCHAR2(32000);
1291: l_filter_str VARCHAR2(4000) := null;
1292: l_prim_temp VARCHAR2(4000) := null;
1293: l_prim_temp_den VARCHAR2(4000) := null;

Line 1290: l_den_contains_str_temp VARCHAR2(32000);

1286: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1287: l_contains_str VARCHAR2(32000);
1288: l_contains_str_temp VARCHAR2(32000);
1289: l_den_contains_str VARCHAR2(32000);
1290: l_den_contains_str_temp VARCHAR2(32000);
1291: l_filter_str VARCHAR2(4000) := null;
1292: l_prim_temp VARCHAR2(4000) := null;
1293: l_prim_temp_den VARCHAR2(4000) := null;
1294: BEGIN

Line 1303: l_contains_str VARCHAR2(32000);

1299: RETURN NULL;
1300: END;
1301:
1302: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1303: l_contains_str VARCHAR2(32000);
1304: l_contains_str_temp VARCHAR2(32000);
1305: l_den_contains_str VARCHAR2(32000);
1306: l_den_contains_str_temp VARCHAR2(32000);
1307: l_filter_str VARCHAR2(4000) := null;

Line 1304: l_contains_str_temp VARCHAR2(32000);

1300: END;
1301:
1302: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1303: l_contains_str VARCHAR2(32000);
1304: l_contains_str_temp VARCHAR2(32000);
1305: l_den_contains_str VARCHAR2(32000);
1306: l_den_contains_str_temp VARCHAR2(32000);
1307: l_filter_str VARCHAR2(4000) := null;
1308: l_prim_temp VARCHAR2(4000) := null;

Line 1305: l_den_contains_str VARCHAR2(32000);

1301:
1302: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1303: l_contains_str VARCHAR2(32000);
1304: l_contains_str_temp VARCHAR2(32000);
1305: l_den_contains_str VARCHAR2(32000);
1306: l_den_contains_str_temp VARCHAR2(32000);
1307: l_filter_str VARCHAR2(4000) := null;
1308: l_prim_temp VARCHAR2(4000) := null;
1309: l_prim_temp_den VARCHAR2(4000) := null;

Line 1306: l_den_contains_str_temp VARCHAR2(32000);

1302: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1303: l_contains_str VARCHAR2(32000);
1304: l_contains_str_temp VARCHAR2(32000);
1305: l_den_contains_str VARCHAR2(32000);
1306: l_den_contains_str_temp VARCHAR2(32000);
1307: l_filter_str VARCHAR2(4000) := null;
1308: l_prim_temp VARCHAR2(4000) := null;
1309: l_prim_temp_den VARCHAR2(4000) := null;
1310: TYPE CONTACT_PT_REC_TYPE IS RECORD (

Line 1329: p_party_contains_str VARCHAR2,

1325: p_dup_party_id NUMBER,
1326: p_restrict_sql VARCHAR2,
1327: p_match_str VARCHAR2,
1328: p_search_merged VARCHAR2,
1329: p_party_contains_str VARCHAR2,
1330: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1331: l_sqlstr VARCHAR2(4000);
1332: l_search_merged VARCHAR2(1);
1333: BEGIN

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

1342: END IF;
1343: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1344: 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);
1345: END IF;
1346: 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
1347: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1348: 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);
1349: END IF;
1350: OPEN x_cursor FOR

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

1384: l_sqlstr := l_sqlstr || ' (TX46 like :TX46||'' %'' ';
1385: l_sqlstr := l_sqlstr || ' )) ';
1386: l_sqlstr := l_sqlstr || ' ) ';
1387: END IF;
1388: 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
1389: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1390: 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);
1391: END IF;
1392: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

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

1386: l_sqlstr := l_sqlstr || ' ) ';
1387: END IF;
1388: 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
1389: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1390: 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);
1391: END IF;
1392: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1393: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1394: l_sqlstr := l_sqlstr || ' )) ';

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

1413: END IF;
1414: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1415: 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);
1416: END IF;
1417: 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
1418: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1419: 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);
1420: END IF;
1421: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id

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

1415: 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);
1416: END IF;
1417: 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
1418: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1419: 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);
1420: END IF;
1421: 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
1422: ,g_party_stage_rec.TX34
1423: ,g_party_stage_rec.TX34

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

1449: ,g_party_stage_rec.TX158
1450: ,g_party_stage_rec.TX46
1451: ,g_party_stage_rec.TX46
1452: ;
1453: 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
1454: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1455: 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);
1456: END IF;
1457: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id

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

1451: ,g_party_stage_rec.TX46
1452: ;
1453: 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
1454: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1455: 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);
1456: END IF;
1457: 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
1458: ,g_party_site_stage_list(1).TX10
1459: ,g_party_site_stage_list(1).TX10

Line 1476: p_contains_str VARCHAR2,

1472: END;
1473: PROCEDURE open_party_cursor(
1474: p_dup_party_id NUMBER,
1475: p_restrict_sql VARCHAR2,
1476: p_contains_str VARCHAR2,
1477: p_search_ctx_id NUMBER,
1478: p_match_str VARCHAR2,
1479: p_search_merged VARCHAR2,
1480: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1495: IF p_contains_str IS NULL THEN

1491: l_search_merged := 'N';
1492: ELSE
1493: l_search_merged := p_search_merged;
1494: END IF;
1495: IF p_contains_str IS NULL THEN
1496: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1497: 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);
1498: END IF;
1499: OPEN x_cursor FOR

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

1493: l_search_merged := p_search_merged;
1494: END IF;
1495: IF p_contains_str IS NULL THEN
1496: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1497: 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);
1498: END IF;
1499: OPEN x_cursor FOR
1500: SELECT PARTY_ID , TX34, TX36, TX40, TX158, TX39, TX46
1501: FROM HZ_STAGED_PARTIES stage

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

1510: END IF;
1511: OPEN x_cursor FOR
1512: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX34, TX36, TX40, TX158, TX39, TX46
1513: FROM HZ_STAGED_PARTIES stage
1514: WHERE contains( concat_col, p_contains_str)>0
1515: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1516: AND( (l_search_merged ='Y' )
1517: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1518: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1523: END IF;
1524: OPEN x_cursor FOR
1525: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX34, TX36, TX40, TX158, TX39, TX46
1526: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1527: WHERE contains( concat_col, p_contains_str)>0
1528: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1529: AND d.party_id = stage.party_id
1530: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1531: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1548: END IF;
1549: IF p_search_ctx_id IS NULL THEN
1550: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX34, TX36, TX40, TX158, TX39, TX46'||
1551: ' FROM HZ_STAGED_PARTIES stage'||
1552: ' WHERE contains( concat_col, :cont)>0'||
1553: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1554: ' AND ('||p_restrict_sql||')' ||
1555: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1556: IF l_search_merged = 'Y' THEN

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

1560: ELSE
1561: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1562: END IF;
1563: output_long_strings('----------------------------------------------------------');
1564: output_long_strings('Party Contains String = '||p_contains_str);
1565: output_long_strings('Restrict Sql = '||p_restrict_sql);
1566: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1567: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1568: ,p_dup_party_id, p_dup_party_id;

Line 1566: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1562: END IF;
1563: output_long_strings('----------------------------------------------------------');
1564: output_long_strings('Party Contains String = '||p_contains_str);
1565: output_long_strings('Restrict Sql = '||p_restrict_sql);
1566: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1567: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1568: ,p_dup_party_id, p_dup_party_id;
1569: END IF;
1570: END IF;

Line 1597: p_contains_str VARCHAR2,

1593: PROCEDURE open_party_site_cursor(
1594: p_dup_party_id NUMBER,
1595: p_party_id NUMBER,
1596: p_restrict_sql VARCHAR2,
1597: p_contains_str VARCHAR2,
1598: p_search_ctx_id NUMBER,
1599: p_search_merged VARCHAR2,
1600: p_search_rel_sites VARCHAR2,
1601: p_person_api VARCHAR2,

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

1625: IF p_search_rel_sites = 'N' THEN
1626: OPEN x_cursor FOR
1627: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX14, TX22
1628: FROM HZ_STAGED_PARTY_SITES stage
1629: WHERE contains( concat_col, p_contains_str)>0
1630: AND( (l_search_merged ='Y' )
1631: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1632: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1633: AND stage.party_id = p_party_id;

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

1634: ELSE
1635: OPEN x_cursor FOR
1636: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX14, TX22
1637: FROM HZ_STAGED_PARTY_SITES stage
1638: WHERE contains( concat_col, p_contains_str)>0
1639: AND( (l_search_merged ='Y' )
1640: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1641: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1642: AND stage.party_id = p_party_id

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

1642: AND stage.party_id = p_party_id
1643: UNION
1644: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX14, TX22
1645: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1646: WHERE contains( concat_col, p_contains_str)>0
1647: AND( (l_search_merged ='Y' )
1648: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1649: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1650: AND r.object_id = p_party_id

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

1664: END IF;
1665: OPEN x_cursor FOR
1666: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX14, TX22
1667: FROM HZ_STAGED_PARTY_SITES stage
1668: WHERE contains( concat_col, p_contains_str)>0
1669: AND EXISTS (
1670: SELECT 1 FROM HZ_STAGED_PARTIES p
1671: WHERE p.PARTY_ID = stage.PARTY_ID
1672: AND( (l_search_merged ='Y' )

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

1684: IF p_person_api = 'Y' THEN
1685: OPEN x_cursor FOR
1686: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX14, TX22
1687: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1688: WHERE contains( concat_col, p_contains_str)>0
1689: AND( (l_search_merged ='Y' )
1690: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1691: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1692: AND d.search_context_id = p_search_ctx_id

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

1694: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1695: UNION
1696: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX14, TX22
1697: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1698: WHERE contains( concat_col, p_contains_str)>0
1699: AND( (l_search_merged ='Y' )
1700: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1701: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1702: AND d.search_context_id = p_search_ctx_id

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

1707: ELSE
1708: OPEN x_cursor FOR
1709: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX14, TX22
1710: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1711: WHERE contains( concat_col, p_contains_str)>0
1712: AND( (l_search_merged ='Y' )
1713: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1714: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1715: AND d.search_context_id = p_search_ctx_id

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

1752: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1753: END IF;
1754: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX14, TX22'||
1755: ' FROM HZ_STAGED_PARTY_SITES stage'||
1756: ' WHERE contains( concat_col, :cont)>0'||
1757: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1758: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1759: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1760: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1762: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1763: ' UNION ' ||
1764: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX14, TX22'||
1765: ' FROM HZ_STAGED_PARTY_SITES stage'||
1766: ' WHERE contains( concat_col, :cont)>0'||
1767: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1768: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1769: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1770: ' AND ORG_CONTACT_ID IN ' ||

Line 1776: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1772: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1773: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1774: ' and ('||p_restrict_sql1|| ') )' ||
1775: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1776: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1777: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1778: ELSE
1779: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX14, TX22'||
1780: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1777: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1773: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1774: ' and ('||p_restrict_sql1|| ') )' ||
1775: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1776: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1777: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1778: ELSE
1779: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX14, TX22'||
1780: ' FROM HZ_STAGED_PARTY_SITES stage'||
1781: ' WHERE contains( concat_col, :cont)>0'||

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

1777: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1778: ELSE
1779: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX14, TX22'||
1780: ' FROM HZ_STAGED_PARTY_SITES stage'||
1781: ' WHERE contains( concat_col, :cont)>0'||
1782: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1783: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1784: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1785: ' AND EXISTS ('||

Line 1791: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1787: ' WHERE p.party_id = stage.party_id ' ||
1788: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1789: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1790: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1791: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1792: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1793: ,p_dup_party_id, p_dup_party_id;
1794: END IF;
1795: ELSE

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

1794: END IF;
1795: ELSE
1796: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX14, TX22'||
1797: ' FROM HZ_STAGED_PARTY_SITES stage'||
1798: ' WHERE contains( concat_col, :cont)>0'||
1799: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1800: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1801: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1802: ' AND EXISTS ('||

Line 1808: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1804: ' WHERE p.party_id = stage.party_id ' ||
1805: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1806: ' AND ('||p_restrict_sql||')' ||
1807: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1808: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1809: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1810: ,p_dup_party_id, p_dup_party_id;
1811: END IF;
1812: END IF;

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

1810: ,p_dup_party_id, p_dup_party_id;
1811: END IF;
1812: END IF;
1813: output_long_strings('----------------------------------------------------------');
1814: output_long_strings('Party Site Contains String = '||p_contains_str);
1815: output_long_strings('Restrict Sql = '||p_restrict_sql);
1816: exception
1817: when others then
1818: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1842: p_contains_str VARCHAR2,

1838: PROCEDURE open_contact_cursor(
1839: p_dup_party_id NUMBER,
1840: p_party_id NUMBER,
1841: p_restrict_sql VARCHAR2,
1842: p_contains_str VARCHAR2,
1843: p_search_ctx_id NUMBER,
1844: p_search_merged VARCHAR2,
1845: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1846: l_sqlstr VARCHAR2(4000);

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

1866: END IF;
1867: OPEN x_cursor FOR
1868: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1869: FROM HZ_STAGED_CONTACTS stage
1870: WHERE contains( concat_col, p_contains_str)>0
1871: AND EXISTS (
1872: SELECT 1 FROM HZ_STAGED_PARTIES p
1873: WHERE p.PARTY_ID = stage.PARTY_ID
1874: AND( (l_search_merged ='Y' )

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

1889: END IF;
1890: OPEN x_cursor FOR
1891: SELECT ORG_CONTACT_ID, PARTY_ID
1892: FROM HZ_STAGED_CONTACTS stage
1893: WHERE contains( concat_col, p_contains_str)>0
1894: AND EXISTS (
1895: SELECT 1 FROM HZ_STAGED_PARTIES p
1896: WHERE p.PARTY_ID = stage.PARTY_ID
1897: AND( (l_search_merged ='Y' )

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

1908: END IF;
1909: OPEN x_cursor FOR
1910: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1911: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1912: WHERE contains( concat_col, p_contains_str)>0
1913: AND d.search_context_id = p_search_ctx_id
1914: AND d.party_id = stage.party_id
1915: AND( (l_search_merged ='Y' )
1916: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1942: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1943: END IF;
1944: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1945: ' FROM HZ_STAGED_CONTACTS stage'||
1946: ' WHERE contains( concat_col, :cont)>0'||
1947: ' AND EXISTS ('||
1948: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1949: ' WHERE p.party_id = stage.party_id ' ||
1950: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1956: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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 ('||p_restrict_sql||')' ||
1955: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1956: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1957: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1958: ,p_dup_party_id, p_dup_party_id;
1959: END IF;
1960: output_long_strings('----------------------------------------------------------');

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

1957: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1958: ,p_dup_party_id, p_dup_party_id;
1959: END IF;
1960: output_long_strings('----------------------------------------------------------');
1961: output_long_strings('Contacts Contains String = '||p_contains_str);
1962: output_long_strings('Restrict Sql = '||p_restrict_sql);
1963: exception
1964: when others then
1965: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1989: p_contains_str VARCHAR2,

1985: PROCEDURE open_contact_pt_cursor(
1986: p_dup_party_id NUMBER,
1987: p_party_id NUMBER,
1988: p_restrict_sql VARCHAR2,
1989: p_contains_str VARCHAR2,
1990: p_search_ctx_id NUMBER,
1991: p_search_merged VARCHAR2,
1992: p_search_rel_cpts VARCHAR2,
1993: p_person_api VARCHAR2,

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

2015: THEN
2016: OPEN x_cursor FOR
2017: 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
2018: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2019: WHERE contains( concat_col, p_contains_str)>0
2020: AND d.search_context_id = p_search_ctx_id
2021: AND( (l_search_merged ='Y' )
2022: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2023: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2027: THEN
2028: OPEN x_cursor FOR
2029: 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
2030: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2031: WHERE contains( concat_col, p_contains_str)>0
2032: AND d.search_context_id = p_search_ctx_id
2033: AND( (l_search_merged ='Y' )
2034: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2035: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2044: IF p_search_rel_cpts = 'N' THEN
2045: OPEN x_cursor FOR
2046: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2047: FROM HZ_STAGED_CONTACT_POINTS stage
2048: WHERE contains( concat_col, p_contains_str)>0
2049: AND( (l_search_merged ='Y' )
2050: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2051: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2052: AND stage.party_id = p_party_id;

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

2053: ELSE
2054: OPEN x_cursor FOR
2055: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2056: FROM HZ_STAGED_CONTACT_POINTS stage
2057: WHERE contains( concat_col, p_contains_str)>0
2058: AND( (l_search_merged ='Y' )
2059: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2060: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2061: AND stage.party_id = p_party_id

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

2061: AND stage.party_id = p_party_id
2062: UNION
2063: 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
2064: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2065: WHERE contains( concat_col, p_contains_str)>0
2066: AND r.object_id = p_party_id
2067: AND( (l_search_merged ='Y' )
2068: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2069: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2083: END IF;
2084: OPEN x_cursor FOR
2085: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2086: FROM HZ_STAGED_CONTACT_POINTS stage
2087: WHERE contains( concat_col, p_contains_str)>0
2088: AND EXISTS (
2089: SELECT 1 FROM HZ_STAGED_PARTIES p
2090: WHERE p.PARTY_ID = stage.PARTY_ID
2091: AND( (l_search_merged ='Y' )

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

2103: IF p_person_api = 'Y' THEN
2104: OPEN x_cursor FOR
2105: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2106: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2107: WHERE contains( concat_col, p_contains_str)>0
2108: AND d.search_context_id = p_search_ctx_id
2109: AND d.party_id = stage.party_id
2110: AND( (l_search_merged ='Y' )
2111: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2113: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2114: UNION
2115: 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
2116: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2117: WHERE contains( concat_col, p_contains_str)>0
2118: AND d.search_context_id = p_search_ctx_id
2119: AND d.party_id = r.subject_id
2120: AND r.relationship_id = oc.party_relationship_id
2121: AND oc.org_contact_id = stage.org_contact_id

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

2126: ELSE
2127: OPEN x_cursor FOR
2128: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2129: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2130: WHERE contains( concat_col, p_contains_str)>0
2131: AND d.search_context_id = p_search_ctx_id
2132: AND d.party_id = stage.party_id
2133: AND( (l_search_merged ='Y' )
2134: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2171: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2172: END IF;
2173: 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 '||
2174: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2175: ' WHERE contains( concat_col, :cont)>0 '||
2176: ' AND (stage.org_contact_id is null '||
2177: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2178: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2179: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

2181: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2182: ' UNION ' ||
2183: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2184: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2185: ' WHERE contains( concat_col, :cont)>0 '||
2186: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2187: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2188: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2189: ' AND (stage.org_contact_id in ' ||

Line 2195: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

2191: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2192: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2193: ' and ('||p_restrict_sql1 || ') ) )' ||
2194: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2195: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2196: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2197: ELSE
2198: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2199: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 2196: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

2192: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2193: ' and ('||p_restrict_sql1 || ') ) )' ||
2194: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2195: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2196: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2197: ELSE
2198: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2199: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2200: ' WHERE contains( concat_col, :cont)>0'||

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

2196: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2197: ELSE
2198: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2199: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2200: ' WHERE contains( concat_col, :cont)>0'||
2201: ' AND EXISTS ('||
2202: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2203: ' WHERE p.party_id = stage.party_id ' ||
2204: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2210: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2206: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2207: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2208: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2209: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2210: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2211: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2212: ,p_dup_party_id, p_dup_party_id;
2213: END IF;
2214: ELSE

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

2213: END IF;
2214: ELSE
2215: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2216: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2217: ' WHERE contains( concat_col, :cont)>0'||
2218: ' AND EXISTS ('||
2219: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2220: ' WHERE p.party_id = stage.party_id ' ||
2221: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2227: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2223: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2224: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2225: ' AND ('||p_restrict_sql||')' ||
2226: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2227: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2228: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2229: ,p_dup_party_id, p_dup_party_id;
2230: END IF;
2231: END IF;

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

2230: END IF;
2231: END IF;
2232: END IF;
2233: output_long_strings('----------------------------------------------------------');
2234: output_long_strings('Contact Points Contains String = '||p_contains_str);
2235: output_long_strings('Restrict Sql = '||p_restrict_sql);
2236: exception
2237: when others then
2238: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2355: l_party_contains_str VARCHAR2(32000);

2351: x_num_matches OUT NUMBER
2352: ) IS
2353:
2354: -- Strings to hold the generated Intermedia query strings
2355: l_party_contains_str VARCHAR2(32000);
2356: l_party_site_contains_str VARCHAR2(32000);
2357: l_contact_contains_str VARCHAR2(32000);
2358: l_contact_pt_contains_str VARCHAR2(32000);
2359: l_denorm_str VARCHAR2(32000);

Line 2356: l_party_site_contains_str VARCHAR2(32000);

2352: ) IS
2353:
2354: -- Strings to hold the generated Intermedia query strings
2355: l_party_contains_str VARCHAR2(32000);
2356: l_party_site_contains_str VARCHAR2(32000);
2357: l_contact_contains_str VARCHAR2(32000);
2358: l_contact_pt_contains_str VARCHAR2(32000);
2359: l_denorm_str VARCHAR2(32000);
2360: l_ps_denorm_str VARCHAR2(32000);

Line 2357: l_contact_contains_str VARCHAR2(32000);

2353:
2354: -- Strings to hold the generated Intermedia query strings
2355: l_party_contains_str VARCHAR2(32000);
2356: l_party_site_contains_str VARCHAR2(32000);
2357: l_contact_contains_str VARCHAR2(32000);
2358: l_contact_pt_contains_str VARCHAR2(32000);
2359: l_denorm_str VARCHAR2(32000);
2360: l_ps_denorm_str VARCHAR2(32000);
2361: l_ct_denorm_str VARCHAR2(32000);

Line 2358: l_contact_pt_contains_str VARCHAR2(32000);

2354: -- Strings to hold the generated Intermedia query strings
2355: l_party_contains_str VARCHAR2(32000);
2356: l_party_site_contains_str VARCHAR2(32000);
2357: l_contact_contains_str VARCHAR2(32000);
2358: l_contact_pt_contains_str VARCHAR2(32000);
2359: l_denorm_str VARCHAR2(32000);
2360: l_ps_denorm_str VARCHAR2(32000);
2361: l_ct_denorm_str VARCHAR2(32000);
2362: l_cpt_denorm_str VARCHAR2(32000);

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

2451: call_type(2) := 'OR';
2452: END;
2453:
2454: /** Private procedure to acquire and score at party level ***/
2455: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2456: l_party_id_idx NUMBER:=1;
2457: l_ctx_id NUMBER;
2458: l_precalc_score BOOLEAN := FALSE;
2459: l_TX35_new varchar2(4000);

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

2483: l_search_ctx_id,H_PARTY_ID(I));
2484: l_party_id_idx := H_PARTY_ID.COUNT+1;
2485: ELSE
2486: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2487: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2488: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2489: 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);
2490: END IF;
2491: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' AND p_dup_batch_id IS NULL THEN

Line 2495: IF (l_party_site_contains_str IS NULL AND

2491: 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
2492: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2493: 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);
2494: END IF;
2495: IF (l_party_site_contains_str IS NULL AND
2496: l_contact_contains_str IS NULL AND
2497: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2498: 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);
2499: RAISE L_RETURN_IMM_EXC;

Line 2496: l_contact_contains_str IS NULL AND

2492: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2493: 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);
2494: END IF;
2495: IF (l_party_site_contains_str IS NULL AND
2496: l_contact_contains_str IS NULL AND
2497: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2498: 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);
2499: RAISE L_RETURN_IMM_EXC;
2500: ELSE

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

2493: 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);
2494: END IF;
2495: IF (l_party_site_contains_str IS NULL AND
2496: l_contact_contains_str IS NULL AND
2497: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2498: 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);
2499: RAISE L_RETURN_IMM_EXC;
2500: ELSE
2501: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

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

2497: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2498: 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);
2499: RAISE L_RETURN_IMM_EXC;
2500: ELSE
2501: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2502: END IF;
2503: ELSE
2504: IF p_index>1 THEN
2505: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2520: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2521: 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);
2522: END IF;
2523: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2524: 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);
2525: END IF;
2526: LOOP
2527: FETCH l_party_cur INTO
2528: l_party_id , l_TX34, l_TX36, l_TX40, l_TX158, l_TX39, l_TX46;

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

2542: l_party_id_idx:= l_party_id_idx+1;
2543: END IF;
2544: IF (l_party_id_idx-1)>l_max_thresh THEN
2545: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2546: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2547: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2548: AND l_contact_pt_contains_str IS NULL THEN
2549: EXIT;
2550: END IF;

Line 2548: AND l_contact_pt_contains_str IS NULL THEN

2544: IF (l_party_id_idx-1)>l_max_thresh THEN
2545: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2546: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2547: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2548: AND l_contact_pt_contains_str IS NULL THEN
2549: EXIT;
2550: END IF;
2551: CLOSE l_party_cur;
2552: IF p_index>1 THEN

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

2573: ROLLBACK to eval_start;
2574: END;
2575:
2576: /** Private procedure to acquire and score at party site level ***/
2577: 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
2578: l_party_id_idx NUMBER:=1;
2579: l_ctx_id NUMBER;
2580: h_ps_id HZ_PARTY_SEARCH.IDList;
2581: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2585: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2586: 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);
2587: 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);
2588: END IF;
2589: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2590: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2591: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2592: l_party_id := H_SCORES.FIRST;
2593: WHILE l_party_id IS NOT NULL LOOP

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

2586: 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);
2587: 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);
2588: END IF;
2589: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2590: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2591: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2592: l_party_id := H_SCORES.FIRST;
2593: WHILE l_party_id IS NOT NULL LOOP
2594: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2587: 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);
2588: END IF;
2589: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2590: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2591: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2592: l_party_id := H_SCORES.FIRST;
2593: WHILE l_party_id IS NOT NULL LOOP
2594: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2595: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2617: l_search_ctx_id,H_PARTY_ID(I));
2618: l_party_id_idx := H_PARTY_ID.COUNT+1;
2619: ELSE
2620: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2621: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2622: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2623: 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);
2624: END IF;
2625: IF p_index>1 THEN

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

2639: END IF;
2640: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2641: 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);
2642: END IF;
2643: 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);
2644: LOOP
2645: FETCH l_party_site_cur INTO
2646: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX14, l_TX22;
2647: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2695: END IF;
2696: END;
2697:
2698: /** Private procedure to acquire and score at contact point level ***/
2699: 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
2700: l_party_id_idx NUMBER:=1;
2701: l_ctx_id NUMBER;
2702: h_ct_id HZ_PARTY_SEARCH.IDList;
2703: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2729: l_search_ctx_id,H_PARTY_ID(I));
2730: l_party_id_idx := H_PARTY_ID.COUNT+1;
2731: ELSE
2732: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2733: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2734: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2735: 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);
2736: END IF;
2737: IF p_index>1 THEN

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

2751: END IF;
2752: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2753: 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);
2754: END IF;
2755: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2756: LOOP
2757: FETCH l_contact_cur INTO
2758: l_org_contact_id, l_ct_party_id ;
2759: EXIT WHEN l_contact_cur%NOTFOUND;

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

2804: END IF;
2805: END;
2806:
2807: /** Private procedure to acquire and score at contact point level ***/
2808: 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
2809: l_party_id_idx NUMBER:=1;
2810: l_ctx_id NUMBER;
2811: h_cpt_id HZ_PARTY_SEARCH.IDList;
2812: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2840: l_search_ctx_id,H_PARTY_ID(I));
2841: l_party_id_idx := H_PARTY_ID.COUNT+1;
2842: ELSE
2843: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2844: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2845: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2846: 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);
2847: END IF;
2848: IF p_index>1 THEN

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

2862: END IF;
2863: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2864: 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);
2865: END IF;
2866: 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);
2867: LOOP
2868: FETCH l_contact_pt_cur INTO
2869: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
2870: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3047: IF l_party_contains_str IS NOT NULL THEN

3043: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3044: l_threshold := round(( l_entered_max_score / 215) * 71);
3045: other_acq_criteria_exists := TRUE ;
3046: --check if acquisition criteria exists for any other entity
3047: IF l_party_contains_str IS NOT NULL THEN
3048: acq_cnt := 1;
3049: END IF;
3050: IF l_party_site_contains_str IS NOT NULL THEN
3051: acq_cnt := acq_cnt+1;

Line 3050: IF l_party_site_contains_str IS NOT NULL THEN

3046: --check if acquisition criteria exists for any other entity
3047: IF l_party_contains_str IS NOT NULL THEN
3048: acq_cnt := 1;
3049: END IF;
3050: IF l_party_site_contains_str IS NOT NULL THEN
3051: acq_cnt := acq_cnt+1;
3052: END IF;
3053: IF l_contact_contains_str IS NOT NULL THEN
3054: acq_cnt := acq_cnt+1;

Line 3053: IF l_contact_contains_str IS NOT NULL THEN

3049: END IF;
3050: IF l_party_site_contains_str IS NOT NULL THEN
3051: acq_cnt := acq_cnt+1;
3052: END IF;
3053: IF l_contact_contains_str IS NOT NULL THEN
3054: acq_cnt := acq_cnt+1;
3055: END IF;
3056: IF l_contact_pt_contains_str IS NOT NULL THEN
3057: acq_cnt := acq_cnt+1;

Line 3056: IF l_contact_pt_contains_str IS NOT NULL THEN

3052: END IF;
3053: IF l_contact_contains_str IS NOT NULL THEN
3054: acq_cnt := acq_cnt+1;
3055: END IF;
3056: IF l_contact_pt_contains_str IS NOT NULL THEN
3057: acq_cnt := acq_cnt+1;
3058: END IF;
3059: IF acq_cnt>1 THEN
3060: other_acq_criteria_exists := TRUE;

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

3086: FND_MSG_PUB.ADD;
3087: RAISE FND_API.G_EXC_ERROR;
3088: END IF;
3089: END IF;
3090: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3091: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3092: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3093: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3094: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

3087: RAISE FND_API.G_EXC_ERROR;
3088: END IF;
3089: END IF;
3090: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3091: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3092: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3093: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3094: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3095: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3088: END IF;
3089: END IF;
3090: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3091: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3092: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3093: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3094: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3095: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3096: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3089: END IF;
3090: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3091: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3092: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3093: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3094: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3095: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3096: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3097: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3090: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3091: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3092: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3093: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3094: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3095: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3096: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3097: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3098: END IF;

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

3091: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3092: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3093: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3094: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3095: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3096: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3097: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3098: END IF;
3099: END;

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

3092: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3093: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3094: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3095: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3096: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3097: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3098: END IF;
3099: END;
3100: /************ End of find_parties private procedures **********/

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

3093: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3094: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3095: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3096: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3097: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3098: END IF;
3099: END;
3100: /************ End of find_parties private procedures **********/
3101:

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

3165: 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);
3166: END IF;
3167: -- ************************************************************
3168: -- STEP 2. Setup of intermedia query strings for Acquisition query
3169: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3170: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3171: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3172: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3173: hz_utility_v2pub.debug(p_message=>'Commencing the DENORM LOGIC in find_parties',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3166: END IF;
3167: -- ************************************************************
3168: -- STEP 2. Setup of intermedia query strings for Acquisition query
3169: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3170: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3171: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3172: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3173: 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);
3174: END IF;

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

3167: -- ************************************************************
3168: -- STEP 2. Setup of intermedia query strings for Acquisition query
3169: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3170: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3171: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3172: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3173: 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);
3174: END IF;
3175: l_denorm_max_score:=0;

Line 3203: 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((71/215)*l_entered_max_score));

3199: END IF;
3200: ELSE
3201: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3202: END IF;
3203: 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((71/215)*l_entered_max_score));
3204: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3205:
3206: -- Setup Search Context ID
3207: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3213: IF l_party_contains_str IS NULL THEN

3209:
3210: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3211: 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);
3212: END IF;
3213: IF l_party_contains_str IS NULL THEN
3214: defpt := 1;
3215: END IF;
3216: IF l_party_site_contains_str IS NULL THEN
3217: defps := 1;

Line 3216: IF l_party_site_contains_str IS NULL THEN

3212: END IF;
3213: IF l_party_contains_str IS NULL THEN
3214: defpt := 1;
3215: END IF;
3216: IF l_party_site_contains_str IS NULL THEN
3217: defps := 1;
3218: END IF;
3219: IF l_contact_contains_str IS NULL THEN
3220: defct := 1;

Line 3219: IF l_contact_contains_str IS NULL THEN

3215: END IF;
3216: IF l_party_site_contains_str IS NULL THEN
3217: defps := 1;
3218: END IF;
3219: IF l_contact_contains_str IS NULL THEN
3220: defct := 1;
3221: END IF;
3222: IF l_contact_pt_contains_str IS NULL THEN
3223: defcpt := 1;

Line 3222: IF l_contact_pt_contains_str IS NULL THEN

3218: END IF;
3219: IF l_contact_contains_str IS NULL THEN
3220: defct := 1;
3221: END IF;
3222: IF l_contact_pt_contains_str IS NULL THEN
3223: defcpt := 1;
3224: END IF;
3225:
3226: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3227: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3228: 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);
3229: END IF;
3230: 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);
3231: 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);
3232: 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);
3233: 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);
3234: 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);
3235: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3228: 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);
3229: END IF;
3230: 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);
3231: 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);
3232: 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);
3233: 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);
3234: 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);
3235: 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);
3236: END IF;

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

3229: END IF;
3230: 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);
3231: 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);
3232: 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);
3233: 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);
3234: 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);
3235: 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);
3236: END IF;
3237: FOR I in 1..5 LOOP

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

3230: 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);
3231: 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);
3232: 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);
3233: 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);
3234: 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);
3235: 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);
3236: END IF;
3237: FOR I in 1..5 LOOP
3238: do_eval(I);

Line 3316: l_party_contains_str VARCHAR2(32000);

3312: x_num_matches OUT NUMBER
3313: ) IS
3314:
3315: -- Strings to hold the generated Intermedia query strings
3316: l_party_contains_str VARCHAR2(32000);
3317: l_party_site_contains_str VARCHAR2(32000);
3318: l_contact_contains_str VARCHAR2(32000);
3319: l_contact_pt_contains_str VARCHAR2(32000);
3320: l_denorm_str VARCHAR2(32000);

Line 3317: l_party_site_contains_str VARCHAR2(32000);

3313: ) IS
3314:
3315: -- Strings to hold the generated Intermedia query strings
3316: l_party_contains_str VARCHAR2(32000);
3317: l_party_site_contains_str VARCHAR2(32000);
3318: l_contact_contains_str VARCHAR2(32000);
3319: l_contact_pt_contains_str VARCHAR2(32000);
3320: l_denorm_str VARCHAR2(32000);
3321: l_ps_denorm_str VARCHAR2(32000);

Line 3318: l_contact_contains_str VARCHAR2(32000);

3314:
3315: -- Strings to hold the generated Intermedia query strings
3316: l_party_contains_str VARCHAR2(32000);
3317: l_party_site_contains_str VARCHAR2(32000);
3318: l_contact_contains_str VARCHAR2(32000);
3319: l_contact_pt_contains_str VARCHAR2(32000);
3320: l_denorm_str VARCHAR2(32000);
3321: l_ps_denorm_str VARCHAR2(32000);
3322: l_ct_denorm_str VARCHAR2(32000);

Line 3319: l_contact_pt_contains_str VARCHAR2(32000);

3315: -- Strings to hold the generated Intermedia query strings
3316: l_party_contains_str VARCHAR2(32000);
3317: l_party_site_contains_str VARCHAR2(32000);
3318: l_contact_contains_str VARCHAR2(32000);
3319: l_contact_pt_contains_str VARCHAR2(32000);
3320: l_denorm_str VARCHAR2(32000);
3321: l_ps_denorm_str VARCHAR2(32000);
3322: l_ct_denorm_str VARCHAR2(32000);
3323: l_cpt_denorm_str VARCHAR2(32000);

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

3442: call_type(2) := 'OR';
3443: END;
3444:
3445: /** Private procedure to acquire and score at party level ***/
3446: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3447: l_party_id_idx NUMBER:=1;
3448: l_ctx_id NUMBER;
3449: l_TX35_new varchar2(4000);
3450: BEGIN

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

3464: l_search_ctx_id,H_PARTY_ID(I));
3465: l_party_id_idx := H_PARTY_ID.COUNT+1;
3466: ELSE
3467: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3468: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3469: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3470: 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);
3471: END IF;
3472: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3476: IF (l_party_site_contains_str IS NULL AND

3472: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3473: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3474: 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);
3475: END IF;
3476: IF (l_party_site_contains_str IS NULL AND
3477: l_contact_contains_str IS NULL AND
3478: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3479: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3480: RAISE L_RETURN_IMM_EXC;

Line 3477: l_contact_contains_str IS NULL AND

3473: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3474: 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);
3475: END IF;
3476: IF (l_party_site_contains_str IS NULL AND
3477: l_contact_contains_str IS NULL AND
3478: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3479: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3480: RAISE L_RETURN_IMM_EXC;
3481: ELSE

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

3474: 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);
3475: END IF;
3476: IF (l_party_site_contains_str IS NULL AND
3477: l_contact_contains_str IS NULL AND
3478: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3479: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3480: RAISE L_RETURN_IMM_EXC;
3481: ELSE
3482: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3478: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3479: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3480: RAISE L_RETURN_IMM_EXC;
3481: ELSE
3482: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3483: END IF;
3484: ELSE
3485: IF p_index>1 THEN
3486: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3498: l_ctx_id := NULL;
3499: l_party_id_idx := H_PARTY_ID.COUNT+1;
3500: END IF;
3501: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3502: 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);
3503: END IF;
3504: LOOP
3505: FETCH l_party_cur INTO
3506: l_party_id , l_TX34, l_TX36, l_TX40, l_TX158, l_TX39, l_TX46;

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

3520: l_party_id_idx:= l_party_id_idx+1;
3521: END IF;
3522: IF (l_party_id_idx-1)>l_max_thresh THEN
3523: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3524: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3525: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3526: AND l_contact_pt_contains_str IS NULL THEN
3527: H_PARTY_ID.DELETE(l_party_id_idx-1);
3528: H_PARTY_ID_LIST.DELETE(l_index);

Line 3526: AND l_contact_pt_contains_str IS NULL THEN

3522: IF (l_party_id_idx-1)>l_max_thresh THEN
3523: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3524: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3525: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3526: AND l_contact_pt_contains_str IS NULL THEN
3527: H_PARTY_ID.DELETE(l_party_id_idx-1);
3528: H_PARTY_ID_LIST.DELETE(l_index);
3529: H_SCORES.DELETE(l_index);
3530: EXIT;

Line 3555: p_contains_str VARCHAR2,

3551: CLOSE l_party_cur;
3552: ROLLBACK to eval_start;
3553: END;
3554: PROCEDURE open_person_contact_cursor(
3555: p_contains_str VARCHAR2,
3556: p_search_ctx_id NUMBER,
3557: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3558: BEGIN
3559: OPEN x_cursor FOR

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

3558: BEGIN
3559: OPEN x_cursor FOR
3560: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3561: FROM HZ_STAGED_CONTACTS stage
3562: WHERE contains( concat_col, p_contains_str)>0
3563: AND ORG_CONTACT_ID in (
3564: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3565: org_contact_id
3566: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3582: end if;
3583: END;
3584:
3585: /** Private procedure to acquire and score at party site level ***/
3586: 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
3587: l_party_id_idx NUMBER:=1;
3588: l_ctx_id NUMBER;
3589: h_ps_id HZ_PARTY_SEARCH.IDList;
3590: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3610: ELSE
3611: l_party_id_idx := H_PARTY_ID.COUNT+1;
3612: l_ctx_id := NULL;
3613: END IF;
3614: 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);
3615: LOOP
3616: FETCH l_party_site_cur INTO
3617: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX14, l_TX22;
3618: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3681: END IF;
3682: END;
3683:
3684: /** Private procedure to acquire and score at party site level ***/
3685: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3686: l_party_id_idx NUMBER:=1;
3687: l_ctx_id NUMBER;
3688: h_ct_id HZ_PARTY_SEARCH.IDList;
3689: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3697: FORALL I in 1..H_PARTY_ID.COUNT
3698: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3699: l_search_ctx_id,H_PARTY_ID(I));
3700: l_party_id_idx := H_PARTY_ID.COUNT+1;
3701: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3702: LOOP
3703: FETCH l_contact_cur INTO
3704: l_org_contact_id, l_ct_party_id ;
3705: EXIT WHEN l_contact_cur%NOTFOUND;

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

3739: END IF;
3740: END;
3741:
3742: /** Private procedure to acquire and score at contact point level ***/
3743: 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
3744: l_party_id_idx NUMBER:=1;
3745: l_ctx_id NUMBER;
3746: h_cpt_id HZ_PARTY_SEARCH.IDList;
3747: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3784: END IF;
3785: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3786: 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);
3787: END IF;
3788: 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);
3789: LOOP
3790: FETCH l_contact_pt_cur INTO
3791: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3792: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3991: IF l_party_contains_str IS NOT NULL THEN

3987: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3988: l_threshold := round(( l_entered_max_score / 215) * 71);
3989: other_acq_criteria_exists := TRUE ;
3990: --check if acquisition criteria exists for any other entity
3991: IF l_party_contains_str IS NOT NULL THEN
3992: acq_cnt := 1;
3993: END IF;
3994: IF l_party_site_contains_str IS NOT NULL THEN
3995: acq_cnt := acq_cnt+1;

Line 3994: IF l_party_site_contains_str IS NOT NULL THEN

3990: --check if acquisition criteria exists for any other entity
3991: IF l_party_contains_str IS NOT NULL THEN
3992: acq_cnt := 1;
3993: END IF;
3994: IF l_party_site_contains_str IS NOT NULL THEN
3995: acq_cnt := acq_cnt+1;
3996: END IF;
3997: IF l_contact_contains_str IS NOT NULL THEN
3998: acq_cnt := acq_cnt+1;

Line 3997: IF l_contact_contains_str IS NOT NULL THEN

3993: END IF;
3994: IF l_party_site_contains_str IS NOT NULL THEN
3995: acq_cnt := acq_cnt+1;
3996: END IF;
3997: IF l_contact_contains_str IS NOT NULL THEN
3998: acq_cnt := acq_cnt+1;
3999: END IF;
4000: IF l_contact_pt_contains_str IS NOT NULL THEN
4001: acq_cnt := acq_cnt+1;

Line 4000: IF l_contact_pt_contains_str IS NOT NULL THEN

3996: END IF;
3997: IF l_contact_contains_str IS NOT NULL THEN
3998: acq_cnt := acq_cnt+1;
3999: END IF;
4000: IF l_contact_pt_contains_str IS NOT NULL THEN
4001: acq_cnt := acq_cnt+1;
4002: END IF;
4003: IF acq_cnt>1 THEN
4004: other_acq_criteria_exists := TRUE;

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

4030: FND_MSG_PUB.ADD;
4031: RAISE FND_API.G_EXC_ERROR;
4032: END IF;
4033: END IF;
4034: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4035: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4036: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4037: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4038: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

4031: RAISE FND_API.G_EXC_ERROR;
4032: END IF;
4033: END IF;
4034: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4035: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4036: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4037: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4038: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4039: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

4032: END IF;
4033: END IF;
4034: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4035: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4036: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4037: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4038: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4039: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4040: END IF;

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

4033: END IF;
4034: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4035: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4036: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4037: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4038: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4039: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4040: END IF;
4041: END;

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

4034: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
4035: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4036: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4037: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4038: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4039: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4040: END IF;
4041: END;
4042: /************ End of find_persons private procedures **********/

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

4035: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
4036: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
4037: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4038: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
4039: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
4040: END IF;
4041: END;
4042: /************ End of find_persons private procedures **********/
4043:

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

4070: g_score_until_thresh := false;
4071: END IF;
4072: -- ************************************************************
4073: -- STEP 2. Setup of intermedia query strings for Acquisition query
4074: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4075: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4076: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4077: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4078: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

4071: END IF;
4072: -- ************************************************************
4073: -- STEP 2. Setup of intermedia query strings for Acquisition query
4074: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4075: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4076: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4077: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4078: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4079:

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

4072: -- ************************************************************
4073: -- STEP 2. Setup of intermedia query strings for Acquisition query
4074: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4075: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4076: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4077: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4078: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4079:
4080: -- Setup Search Context ID

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

4073: -- STEP 2. Setup of intermedia query strings for Acquisition query
4074: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
4075: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
4076: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
4077: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
4078: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4079:
4080: -- Setup Search Context ID
4081: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 4084: IF l_party_contains_str IS NULL THEN

4080: -- Setup Search Context ID
4081: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4082: x_search_ctx_id := l_search_ctx_id;
4083:
4084: IF l_party_contains_str IS NULL THEN
4085: defpt := 1;
4086: END IF;
4087: IF l_party_site_contains_str IS NULL THEN
4088: defps := 1;

Line 4087: IF l_party_site_contains_str IS NULL THEN

4083:
4084: IF l_party_contains_str IS NULL THEN
4085: defpt := 1;
4086: END IF;
4087: IF l_party_site_contains_str IS NULL THEN
4088: defps := 1;
4089: END IF;
4090: IF l_contact_contains_str IS NULL THEN
4091: defct := 1;

Line 4090: IF l_contact_contains_str IS NULL THEN

4086: END IF;
4087: IF l_party_site_contains_str IS NULL THEN
4088: defps := 1;
4089: END IF;
4090: IF l_contact_contains_str IS NULL THEN
4091: defct := 1;
4092: END IF;
4093: IF l_contact_pt_contains_str IS NULL THEN
4094: defcpt := 1;

Line 4093: IF l_contact_pt_contains_str IS NULL THEN

4089: END IF;
4090: IF l_contact_contains_str IS NULL THEN
4091: defct := 1;
4092: END IF;
4093: IF l_contact_pt_contains_str IS NULL THEN
4094: defcpt := 1;
4095: END IF;
4096:
4097: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

4095: END IF;
4096:
4097: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4098: 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);
4099: 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);
4100: 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);
4101: 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);
4102: 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);
4103: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

4096:
4097: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4098: 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);
4099: 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);
4100: 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);
4101: 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);
4102: 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);
4103: 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);
4104: END IF;

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

4097: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4098: 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);
4099: 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);
4100: 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);
4101: 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);
4102: 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);
4103: 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);
4104: END IF;
4105: FOR I in 1..5 LOOP

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

4098: 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);
4099: 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);
4100: 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);
4101: 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);
4102: 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);
4103: 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);
4104: END IF;
4105: FOR I in 1..5 LOOP
4106: do_eval(I);

Line 4108: IF l_contact_contains_str IS NOT NULL THEN

4104: END IF;
4105: FOR I in 1..5 LOOP
4106: do_eval(I);
4107: END LOOP;
4108: IF l_contact_contains_str IS NOT NULL THEN
4109: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4110: END IF;
4111: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4112: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 33 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

4105: FOR I in 1..5 LOOP
4106: do_eval(I);
4107: END LOOP;
4108: IF l_contact_contains_str IS NOT NULL THEN
4109: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4110: END IF;
4111: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4112: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 33 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4113: END IF;

Line 4891: l_party_contains_str VARCHAR2(32000);

4887: x_num_matches OUT NUMBER
4888: ) IS
4889:
4890: -- Strings to hold the generated Intermedia query strings
4891: l_party_contains_str VARCHAR2(32000);
4892: l_match_idx NUMBER;
4893: l_party_site_contains_str VARCHAR2(32000);
4894: l_contact_contains_str VARCHAR2(32000);
4895: l_contact_pt_contains_str VARCHAR2(32000);

Line 4893: l_party_site_contains_str VARCHAR2(32000);

4889:
4890: -- Strings to hold the generated Intermedia query strings
4891: l_party_contains_str VARCHAR2(32000);
4892: l_match_idx NUMBER;
4893: l_party_site_contains_str VARCHAR2(32000);
4894: l_contact_contains_str VARCHAR2(32000);
4895: l_contact_pt_contains_str VARCHAR2(32000);
4896: l_tmp VARCHAR2(32000);
4897:

Line 4894: l_contact_contains_str VARCHAR2(32000);

4890: -- Strings to hold the generated Intermedia query strings
4891: l_party_contains_str VARCHAR2(32000);
4892: l_match_idx NUMBER;
4893: l_party_site_contains_str VARCHAR2(32000);
4894: l_contact_contains_str VARCHAR2(32000);
4895: l_contact_pt_contains_str VARCHAR2(32000);
4896: l_tmp VARCHAR2(32000);
4897:
4898: -- Other local variables

Line 4895: l_contact_pt_contains_str VARCHAR2(32000);

4891: l_party_contains_str VARCHAR2(32000);
4892: l_match_idx NUMBER;
4893: l_party_site_contains_str VARCHAR2(32000);
4894: l_contact_contains_str VARCHAR2(32000);
4895: l_contact_pt_contains_str VARCHAR2(32000);
4896: l_tmp VARCHAR2(32000);
4897:
4898: -- Other local variables
4899: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4970: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4971:
4972: -- ************************************************************
4973: -- STEP 2. Setup of intermedia query strings for Acquisition query
4974: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4975: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4976: 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);
4977:
4978: -- Setup Search Context ID

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

4971:
4972: -- ************************************************************
4973: -- STEP 2. Setup of intermedia query strings for Acquisition query
4974: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4975: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4976: 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);
4977:
4978: -- Setup Search Context ID
4979: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4980: x_search_ctx_id := l_search_ctx_id;
4981:
4982: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4983: 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);
4984: 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);
4985: 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);
4986: 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);
4987: END IF;
4988: IF l_party_site_contains_str IS NULL THEN

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

4981:
4982: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4983: 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);
4984: 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);
4985: 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);
4986: 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);
4987: END IF;
4988: IF l_party_site_contains_str IS NULL THEN
4989: defps := 1;

Line 4988: IF l_party_site_contains_str IS NULL THEN

4984: 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);
4985: 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);
4986: 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);
4987: END IF;
4988: IF l_party_site_contains_str IS NULL THEN
4989: defps := 1;
4990: END IF;
4991: IF l_contact_pt_contains_str IS NULL THEN
4992: defcpt := 1;

Line 4991: IF l_contact_pt_contains_str IS NULL THEN

4987: END IF;
4988: IF l_party_site_contains_str IS NULL THEN
4989: defps := 1;
4990: END IF;
4991: IF l_contact_pt_contains_str IS NULL THEN
4992: defcpt := 1;
4993: END IF;
4994:
4995: IF l_party_site_contains_str IS NOT NULL THEN

Line 4995: IF l_party_site_contains_str IS NOT NULL THEN

4991: IF l_contact_pt_contains_str IS NULL THEN
4992: defcpt := 1;
4993: END IF;
4994:
4995: IF l_party_site_contains_str IS NOT NULL THEN
4996: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4997: LOOP
4998: FETCH l_party_site_cur INTO
4999: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX14, l_TX22;

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

4992: defcpt := 1;
4993: END IF;
4994:
4995: IF l_party_site_contains_str IS NOT NULL THEN
4996: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4997: LOOP
4998: FETCH l_party_site_cur INTO
4999: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX14, l_TX22;
5000: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 5019: IF l_contact_pt_contains_str IS NOT NULL THEN

5015: END LOOP;
5016: CLOSE l_party_site_cur;
5017: END IF;
5018:
5019: IF l_contact_pt_contains_str IS NOT NULL THEN
5020: SAVEPOINT eval_start;
5021: IF l_match_str = ' AND ' OR (
5022: ((l_cpt_max_score/l_entered_max_score)<(71/215))
5023: ) THEN

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

5023: ) THEN
5024: FORALL I in 1..l_match_ps_list.COUNT
5025: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5026: values (l_search_ctx_id,l_match_ps_list(I));
5027: 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');
5028: ELSE
5029: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5030: END IF;
5031: LOOP

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

5025: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5026: values (l_search_ctx_id,l_match_ps_list(I));
5027: 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');
5028: ELSE
5029: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5030: END IF;
5031: LOOP
5032: FETCH l_contact_pt_cur INTO
5033: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 5129: l_party_contains_str VARCHAR2(32000);

5125: x_num_matches OUT NUMBER
5126: ) IS
5127:
5128: -- Strings to hold the generated Intermedia query strings
5129: l_party_contains_str VARCHAR2(32000);
5130: l_party_site_contains_str VARCHAR2(32000);
5131: l_contact_contains_str VARCHAR2(32000);
5132: l_contact_pt_contains_str VARCHAR2(32000);
5133: l_tmp VARCHAR2(32000);

Line 5130: l_party_site_contains_str VARCHAR2(32000);

5126: ) IS
5127:
5128: -- Strings to hold the generated Intermedia query strings
5129: l_party_contains_str VARCHAR2(32000);
5130: l_party_site_contains_str VARCHAR2(32000);
5131: l_contact_contains_str VARCHAR2(32000);
5132: l_contact_pt_contains_str VARCHAR2(32000);
5133: l_tmp VARCHAR2(32000);
5134:

Line 5131: l_contact_contains_str VARCHAR2(32000);

5127:
5128: -- Strings to hold the generated Intermedia query strings
5129: l_party_contains_str VARCHAR2(32000);
5130: l_party_site_contains_str VARCHAR2(32000);
5131: l_contact_contains_str VARCHAR2(32000);
5132: l_contact_pt_contains_str VARCHAR2(32000);
5133: l_tmp VARCHAR2(32000);
5134:
5135: -- Other local variables

Line 5132: l_contact_pt_contains_str VARCHAR2(32000);

5128: -- Strings to hold the generated Intermedia query strings
5129: l_party_contains_str VARCHAR2(32000);
5130: l_party_site_contains_str VARCHAR2(32000);
5131: l_contact_contains_str VARCHAR2(32000);
5132: l_contact_pt_contains_str VARCHAR2(32000);
5133: l_tmp VARCHAR2(32000);
5134:
5135: -- Other local variables
5136: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5199: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5200:
5201: -- ************************************************************
5202: -- STEP 2. Setup of intermedia query strings for Acquisition query
5203: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5204: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5205: 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);
5206:
5207: -- Setup Search Context ID

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

5200:
5201: -- ************************************************************
5202: -- STEP 2. Setup of intermedia query strings for Acquisition query
5203: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5204: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5205: 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);
5206:
5207: -- Setup Search Context ID
5208: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5209: x_search_ctx_id := l_search_ctx_id;
5210:
5211: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5212: 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);
5213: 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);
5214: 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);
5215: 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);
5216: END IF;
5217: IF l_contact_contains_str IS NULL THEN

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

5210:
5211: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5212: 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);
5213: 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);
5214: 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);
5215: 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);
5216: END IF;
5217: IF l_contact_contains_str IS NULL THEN
5218: defct := 1;

Line 5217: IF l_contact_contains_str IS NULL THEN

5213: 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);
5214: 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);
5215: 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);
5216: END IF;
5217: IF l_contact_contains_str IS NULL THEN
5218: defct := 1;
5219: END IF;
5220: IF l_contact_pt_contains_str IS NULL THEN
5221: defcpt := 1;

Line 5220: IF l_contact_pt_contains_str IS NULL THEN

5216: END IF;
5217: IF l_contact_contains_str IS NULL THEN
5218: defct := 1;
5219: END IF;
5220: IF l_contact_pt_contains_str IS NULL THEN
5221: defcpt := 1;
5222: END IF;
5223:
5224: IF l_contact_contains_str IS NOT NULL THEN

Line 5224: IF l_contact_contains_str IS NOT NULL THEN

5220: IF l_contact_pt_contains_str IS NULL THEN
5221: defcpt := 1;
5222: END IF;
5223:
5224: IF l_contact_contains_str IS NOT NULL THEN
5225: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5226: LOOP
5227: FETCH l_contact_cur INTO
5228: l_org_contact_id, l_ct_party_id ;

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

5221: defcpt := 1;
5222: END IF;
5223:
5224: IF l_contact_contains_str IS NOT NULL THEN
5225: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5226: LOOP
5227: FETCH l_contact_cur INTO
5228: l_org_contact_id, l_ct_party_id ;
5229: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5246: IF l_contact_pt_contains_str IS NOT NULL THEN

5242: END LOOP;
5243: CLOSE l_contact_cur;
5244: END IF;
5245:
5246: IF l_contact_pt_contains_str IS NOT NULL THEN
5247: SAVEPOINT eval_start;
5248: IF l_match_str = ' AND ' OR (
5249: ((l_cpt_max_score/l_entered_max_score)<(71/215))
5250: ) THEN

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

5250: ) THEN
5251: FORALL I in 1..l_match_contact_list.COUNT
5252: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5253: values (l_search_ctx_id,l_match_contact_list(I));
5254: 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');
5255: ELSE
5256: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5257: END IF;
5258: LOOP

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

5252: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5253: values (l_search_ctx_id,l_match_contact_list(I));
5254: 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');
5255: ELSE
5256: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5257: END IF;
5258: LOOP
5259: FETCH l_contact_pt_cur INTO
5260: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 5354: l_contact_pt_contains_str VARCHAR2(32000);

5350: ) IS
5351:
5352:
5353: -- Strings to hold the generated Intermedia query strings
5354: l_contact_pt_contains_str VARCHAR2(32000);
5355: l_tmp VARCHAR2(32000);
5356:
5357: -- Other local variables
5358: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5408: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5409:
5410: -- ************************************************************
5411: -- STEP 2. Setup of intermedia query strings for Acquisition query
5412: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5413: 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);
5414:
5415: -- Setup Search Context ID
5416: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5416: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5417: x_search_ctx_id := l_search_ctx_id;
5418: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5419: 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);
5420: 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);
5421: 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);
5422: END IF;
5423:
5424: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 5424: IF l_contact_pt_contains_str IS NOT NULL THEN

5420: 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);
5421: 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);
5422: END IF;
5423:
5424: IF l_contact_pt_contains_str IS NOT NULL THEN
5425: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5426: cnt := 1;
5427: LOOP
5428: FETCH l_contact_pt_cur INTO

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

5421: 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);
5422: END IF;
5423:
5424: IF l_contact_pt_contains_str IS NOT NULL THEN
5425: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5426: cnt := 1;
5427: LOOP
5428: FETCH l_contact_pt_cur INTO
5429: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 5496: l_party_contains_str VARCHAR2(32000);

5492: x_search_ctx_id IN OUT NUMBER
5493: ) IS
5494:
5495: -- Strings to hold the generated Intermedia query strings
5496: l_party_contains_str VARCHAR2(32000);
5497: l_party_site_contains_str VARCHAR2(32000);
5498: l_contact_contains_str VARCHAR2(32000);
5499: l_contact_pt_contains_str VARCHAR2(32000);
5500: l_tmp VARCHAR2(32000);

Line 5497: l_party_site_contains_str VARCHAR2(32000);

5493: ) IS
5494:
5495: -- Strings to hold the generated Intermedia query strings
5496: l_party_contains_str VARCHAR2(32000);
5497: l_party_site_contains_str VARCHAR2(32000);
5498: l_contact_contains_str VARCHAR2(32000);
5499: l_contact_pt_contains_str VARCHAR2(32000);
5500: l_tmp VARCHAR2(32000);
5501:

Line 5498: l_contact_contains_str VARCHAR2(32000);

5494:
5495: -- Strings to hold the generated Intermedia query strings
5496: l_party_contains_str VARCHAR2(32000);
5497: l_party_site_contains_str VARCHAR2(32000);
5498: l_contact_contains_str VARCHAR2(32000);
5499: l_contact_pt_contains_str VARCHAR2(32000);
5500: l_tmp VARCHAR2(32000);
5501:
5502: -- Other local variables

Line 5499: l_contact_pt_contains_str VARCHAR2(32000);

5495: -- Strings to hold the generated Intermedia query strings
5496: l_party_contains_str VARCHAR2(32000);
5497: l_party_site_contains_str VARCHAR2(32000);
5498: l_contact_contains_str VARCHAR2(32000);
5499: l_contact_pt_contains_str VARCHAR2(32000);
5500: l_tmp VARCHAR2(32000);
5501:
5502: -- Other local variables
5503: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5577: g_score_until_thresh := false;
5578: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5579: -- ************************************************************
5580: -- STEP 2. Setup of intermedia query strings for Acquisition query
5581: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5582: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5583: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5584: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5585:

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

5578: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5579: -- ************************************************************
5580: -- STEP 2. Setup of intermedia query strings for Acquisition query
5581: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5582: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5583: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5584: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5585:
5586: -- Setup Search Context ID

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

5579: -- ************************************************************
5580: -- STEP 2. Setup of intermedia query strings for Acquisition query
5581: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5582: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5583: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5584: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5585:
5586: -- Setup Search Context ID
5587: IF x_search_ctx_id IS NULL THEN

Line 5603: IF l_party_site_contains_str IS NOT NULL THEN

5599: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX34,l_TX36,l_TX40,l_TX158,l_TX39,l_TX46,1);
5600: END LOOP;
5601: CLOSE l_party_cur;
5602:
5603: IF l_party_site_contains_str IS NOT NULL THEN
5604: l_max_score := 0;
5605: l_max_id := 0;
5606: l_max_idx := 0;
5607: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5604: l_max_score := 0;
5605: l_max_id := 0;
5606: l_max_idx := 0;
5607: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5608: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5609: ELSE
5610: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5611: END IF;
5612: LOOP

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

5606: l_max_idx := 0;
5607: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5608: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5609: ELSE
5610: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5611: END IF;
5612: LOOP
5613: FETCH l_party_site_cur INTO
5614: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX9, l_TX10, l_TX14, l_TX22;

Line 5635: IF l_contact_contains_str IS NOT NULL THEN

5631: 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_TX14,l_max_TX22,l_max_idx);
5632: END IF;
5633: END IF;
5634:
5635: IF l_contact_contains_str IS NOT NULL THEN
5636: l_max_score := 0;
5637: l_max_id := 0;
5638: l_max_idx := 0;
5639: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5635: IF l_contact_contains_str IS NOT NULL THEN
5636: l_max_score := 0;
5637: l_max_id := 0;
5638: l_max_idx := 0;
5639: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5640: LOOP
5641: FETCH l_contact_cur INTO
5642: l_org_contact_id, l_ct_party_id ;
5643: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5657: IF l_contact_pt_contains_str IS NOT NULL THEN

5653: 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);
5654: END IF;
5655: END IF;
5656:
5657: IF l_contact_pt_contains_str IS NOT NULL THEN
5658: l_max_score := 0;
5659: l_max_id := 0;
5660: l_max_idx := 0;
5661: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5658: l_max_score := 0;
5659: l_max_id := 0;
5660: l_max_idx := 0;
5661: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5662: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5663: ELSE
5664: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5665: END IF;
5666: LOOP

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

5660: l_max_idx := 0;
5661: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5662: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5663: ELSE
5664: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5665: END IF;
5666: LOOP
5667: FETCH l_contact_pt_cur INTO
5668: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;