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

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

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

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

Line 1564: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

Line 1595: p_contains_str VARCHAR2,

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

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

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

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

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

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

1640: AND stage.party_id = p_party_id
1641: UNION
1642: 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
1643: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1644: WHERE contains( concat_col, p_contains_str)>0
1645: AND( (l_search_merged ='Y' )
1646: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1647: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1648: AND r.object_id = p_party_id

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

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

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

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

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

1692: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1693: UNION
1694: 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
1695: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1696: WHERE contains( concat_col, p_contains_str)>0
1697: AND( (l_search_merged ='Y' )
1698: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1699: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1700: AND d.search_context_id = p_search_ctx_id

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

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

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

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

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

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

Line 1774: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 1775: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 1789: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1806: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 1840: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

Line 1954: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1950: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1951: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1952: ' AND ('||p_restrict_sql||')' ||
1953: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1954: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1955: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1956: ,p_dup_party_id, p_dup_party_id;
1957: END IF;
1958: output_long_strings('----------------------------------------------------------');

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

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

Line 1987: p_contains_str VARCHAR2,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 2193: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 2194: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 2208: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 2225: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 2353: l_party_contains_str VARCHAR2(32000);

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

Line 2354: l_party_site_contains_str VARCHAR2(32000);

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

Line 2355: l_contact_contains_str VARCHAR2(32000);

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

Line 2356: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

2480: l_search_ctx_id,H_PARTY_ID(I));
2481: l_party_id_idx := H_PARTY_ID.COUNT+1;
2482: ELSE
2483: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2484: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2485: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2486: 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);
2487: END IF;
2488: 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 2492: IF (l_party_site_contains_str IS NULL AND

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

Line 2493: l_contact_contains_str IS NULL AND

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

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

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

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

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

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

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

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

Line 2545: AND l_contact_pt_contains_str IS NULL THEN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 3044: IF l_party_contains_str IS NOT NULL THEN

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

Line 3047: IF l_party_site_contains_str IS NOT NULL THEN

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

Line 3050: IF l_contact_contains_str IS NOT NULL THEN

3046: END IF;
3047: IF l_party_site_contains_str IS NOT NULL THEN
3048: acq_cnt := acq_cnt+1;
3049: END IF;
3050: IF l_contact_contains_str IS NOT NULL THEN
3051: acq_cnt := acq_cnt+1;
3052: END IF;
3053: IF l_contact_pt_contains_str IS NOT NULL THEN
3054: acq_cnt := acq_cnt+1;

Line 3053: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3196: END IF;
3197: ELSE
3198: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3199: END IF;
3200: 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));
3201: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3202:
3203: -- Setup Search Context ID
3204: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3210: IF l_party_contains_str IS NULL THEN

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

Line 3213: IF l_party_site_contains_str IS NULL THEN

3209: END IF;
3210: IF l_party_contains_str IS NULL THEN
3211: defpt := 1;
3212: END IF;
3213: IF l_party_site_contains_str IS NULL THEN
3214: defps := 1;
3215: END IF;
3216: IF l_contact_contains_str IS NULL THEN
3217: defct := 1;

Line 3216: IF l_contact_contains_str IS NULL THEN

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

Line 3219: IF l_contact_pt_contains_str IS NULL THEN

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

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

3224: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3225: 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);
3226: END IF;
3227: 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);
3228: 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);
3229: 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);
3230: 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);
3231: 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);
3232: 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 3229: 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);

3225: 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);
3226: END IF;
3227: 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);
3228: 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);
3229: 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);
3230: 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);
3231: 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);
3232: 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);
3233: END IF;

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

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

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

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

Line 3313: l_party_contains_str VARCHAR2(32000);

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

Line 3314: l_party_site_contains_str VARCHAR2(32000);

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

Line 3315: l_contact_contains_str VARCHAR2(32000);

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

Line 3316: l_contact_pt_contains_str VARCHAR2(32000);

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

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

3439: call_type(2) := 'OR';
3440: END;
3441:
3442: /** Private procedure to acquire and score at party level ***/
3443: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3444: l_party_id_idx NUMBER:=1;
3445: l_ctx_id NUMBER;
3446: BEGIN
3447: SAVEPOINT eval_start;

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

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

Line 3472: IF (l_party_site_contains_str IS NULL AND

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

Line 3473: l_contact_contains_str IS NULL AND

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

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

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

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

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

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

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

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

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

Line 3522: AND l_contact_pt_contains_str IS NULL THEN

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

Line 3551: p_contains_str VARCHAR2,

3547: CLOSE l_party_cur;
3548: ROLLBACK to eval_start;
3549: END;
3550: PROCEDURE open_person_contact_cursor(
3551: p_contains_str VARCHAR2,
3552: p_search_ctx_id NUMBER,
3553: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3554: BEGIN
3555: OPEN x_cursor FOR

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 3987: IF l_party_contains_str IS NOT NULL THEN

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

Line 3990: IF l_party_site_contains_str IS NOT NULL THEN

3986: --check if acquisition criteria exists for any other entity
3987: IF l_party_contains_str IS NOT NULL THEN
3988: acq_cnt := 1;
3989: END IF;
3990: IF l_party_site_contains_str IS NOT NULL THEN
3991: acq_cnt := acq_cnt+1;
3992: END IF;
3993: IF l_contact_contains_str IS NOT NULL THEN
3994: acq_cnt := acq_cnt+1;

Line 3993: IF l_contact_contains_str IS NOT NULL THEN

3989: END IF;
3990: IF l_party_site_contains_str IS NOT NULL THEN
3991: acq_cnt := acq_cnt+1;
3992: END IF;
3993: IF l_contact_contains_str IS NOT NULL THEN
3994: acq_cnt := acq_cnt+1;
3995: END IF;
3996: IF l_contact_pt_contains_str IS NOT NULL THEN
3997: acq_cnt := acq_cnt+1;

Line 3996: IF l_contact_pt_contains_str IS NOT NULL THEN

3992: END IF;
3993: IF l_contact_contains_str IS NOT NULL THEN
3994: acq_cnt := acq_cnt+1;
3995: END IF;
3996: IF l_contact_pt_contains_str IS NOT NULL THEN
3997: acq_cnt := acq_cnt+1;
3998: END IF;
3999: IF acq_cnt>1 THEN
4000: other_acq_criteria_exists := TRUE;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 4080: IF l_party_contains_str IS NULL THEN

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

Line 4083: IF l_party_site_contains_str IS NULL THEN

4079:
4080: IF l_party_contains_str IS NULL THEN
4081: defpt := 1;
4082: END IF;
4083: IF l_party_site_contains_str IS NULL THEN
4084: defps := 1;
4085: END IF;
4086: IF l_contact_contains_str IS NULL THEN
4087: defct := 1;

Line 4086: IF l_contact_contains_str IS NULL THEN

4082: END IF;
4083: IF l_party_site_contains_str IS NULL THEN
4084: defps := 1;
4085: END IF;
4086: IF l_contact_contains_str IS NULL THEN
4087: defct := 1;
4088: END IF;
4089: IF l_contact_pt_contains_str IS NULL THEN
4090: defcpt := 1;

Line 4089: IF l_contact_pt_contains_str IS NULL THEN

4085: END IF;
4086: IF l_contact_contains_str IS NULL THEN
4087: defct := 1;
4088: END IF;
4089: IF l_contact_pt_contains_str IS NULL THEN
4090: defcpt := 1;
4091: END IF;
4092:
4093: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

4091: END IF;
4092:
4093: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4094: 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);
4095: 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);
4096: 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);
4097: 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);
4098: 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);
4099: 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 4096: 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);

4092:
4093: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4094: 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);
4095: 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);
4096: 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);
4097: 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);
4098: 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);
4099: 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);
4100: END IF;

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

4093: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4094: 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);
4095: 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);
4096: 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);
4097: 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);
4098: 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);
4099: 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);
4100: END IF;
4101: FOR I in 1..5 LOOP

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

4094: 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);
4095: 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);
4096: 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);
4097: 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);
4098: 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);
4099: 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);
4100: END IF;
4101: FOR I in 1..5 LOOP
4102: do_eval(I);

Line 4104: IF l_contact_contains_str IS NOT NULL THEN

4100: END IF;
4101: FOR I in 1..5 LOOP
4102: do_eval(I);
4103: END LOOP;
4104: IF l_contact_contains_str IS NOT NULL THEN
4105: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4106: END IF;
4107: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4108: 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 4105: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);

4101: FOR I in 1..5 LOOP
4102: do_eval(I);
4103: END LOOP;
4104: IF l_contact_contains_str IS NOT NULL THEN
4105: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
4106: END IF;
4107: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4108: 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);
4109: END IF;

Line 4887: l_party_contains_str VARCHAR2(32000);

4883: x_num_matches OUT NUMBER
4884: ) IS
4885:
4886: -- Strings to hold the generated Intermedia query strings
4887: l_party_contains_str VARCHAR2(32000);
4888: l_match_idx NUMBER;
4889: l_party_site_contains_str VARCHAR2(32000);
4890: l_contact_contains_str VARCHAR2(32000);
4891: l_contact_pt_contains_str VARCHAR2(32000);

Line 4889: l_party_site_contains_str VARCHAR2(32000);

4885:
4886: -- Strings to hold the generated Intermedia query strings
4887: l_party_contains_str VARCHAR2(32000);
4888: l_match_idx NUMBER;
4889: l_party_site_contains_str VARCHAR2(32000);
4890: l_contact_contains_str VARCHAR2(32000);
4891: l_contact_pt_contains_str VARCHAR2(32000);
4892: l_tmp VARCHAR2(32000);
4893:

Line 4890: l_contact_contains_str VARCHAR2(32000);

4886: -- Strings to hold the generated Intermedia query strings
4887: l_party_contains_str VARCHAR2(32000);
4888: l_match_idx NUMBER;
4889: l_party_site_contains_str VARCHAR2(32000);
4890: l_contact_contains_str VARCHAR2(32000);
4891: l_contact_pt_contains_str VARCHAR2(32000);
4892: l_tmp VARCHAR2(32000);
4893:
4894: -- Other local variables

Line 4891: l_contact_pt_contains_str VARCHAR2(32000);

4887: l_party_contains_str VARCHAR2(32000);
4888: l_match_idx NUMBER;
4889: l_party_site_contains_str VARCHAR2(32000);
4890: l_contact_contains_str VARCHAR2(32000);
4891: l_contact_pt_contains_str VARCHAR2(32000);
4892: l_tmp VARCHAR2(32000);
4893:
4894: -- Other local variables
4895: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

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

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

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

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

4976: x_search_ctx_id := l_search_ctx_id;
4977:
4978: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4979: 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);
4980: 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);
4981: 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);
4982: 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);
4983: END IF;
4984: IF l_party_site_contains_str IS NULL THEN

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

4977:
4978: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4979: 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);
4980: 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);
4981: 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);
4982: 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);
4983: END IF;
4984: IF l_party_site_contains_str IS NULL THEN
4985: defps := 1;

Line 4984: IF l_party_site_contains_str IS NULL THEN

4980: 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);
4981: 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);
4982: 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);
4983: END IF;
4984: IF l_party_site_contains_str IS NULL THEN
4985: defps := 1;
4986: END IF;
4987: IF l_contact_pt_contains_str IS NULL THEN
4988: defcpt := 1;

Line 4987: IF l_contact_pt_contains_str IS NULL THEN

4983: END IF;
4984: IF l_party_site_contains_str IS NULL THEN
4985: defps := 1;
4986: END IF;
4987: IF l_contact_pt_contains_str IS NULL THEN
4988: defcpt := 1;
4989: END IF;
4990:
4991: IF l_party_site_contains_str IS NOT NULL THEN

Line 4991: IF l_party_site_contains_str IS NOT NULL THEN

4987: IF l_contact_pt_contains_str IS NULL THEN
4988: defcpt := 1;
4989: END IF;
4990:
4991: IF l_party_site_contains_str IS NOT NULL THEN
4992: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4993: LOOP
4994: FETCH l_party_site_cur INTO
4995: 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 4992: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);

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

Line 5015: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

Line 5125: l_party_contains_str VARCHAR2(32000);

5121: x_num_matches OUT NUMBER
5122: ) IS
5123:
5124: -- Strings to hold the generated Intermedia query strings
5125: l_party_contains_str VARCHAR2(32000);
5126: l_party_site_contains_str VARCHAR2(32000);
5127: l_contact_contains_str VARCHAR2(32000);
5128: l_contact_pt_contains_str VARCHAR2(32000);
5129: l_tmp VARCHAR2(32000);

Line 5126: l_party_site_contains_str VARCHAR2(32000);

5122: ) IS
5123:
5124: -- Strings to hold the generated Intermedia query strings
5125: l_party_contains_str VARCHAR2(32000);
5126: l_party_site_contains_str VARCHAR2(32000);
5127: l_contact_contains_str VARCHAR2(32000);
5128: l_contact_pt_contains_str VARCHAR2(32000);
5129: l_tmp VARCHAR2(32000);
5130:

Line 5127: l_contact_contains_str VARCHAR2(32000);

5123:
5124: -- Strings to hold the generated Intermedia query strings
5125: l_party_contains_str VARCHAR2(32000);
5126: l_party_site_contains_str VARCHAR2(32000);
5127: l_contact_contains_str VARCHAR2(32000);
5128: l_contact_pt_contains_str VARCHAR2(32000);
5129: l_tmp VARCHAR2(32000);
5130:
5131: -- Other local variables

Line 5128: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

5205: x_search_ctx_id := l_search_ctx_id;
5206:
5207: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5208: 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);
5209: 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);
5210: 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);
5211: 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);
5212: END IF;
5213: IF l_contact_contains_str IS NULL THEN

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

5206:
5207: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5208: 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);
5209: 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);
5210: 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);
5211: 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);
5212: END IF;
5213: IF l_contact_contains_str IS NULL THEN
5214: defct := 1;

Line 5213: IF l_contact_contains_str IS NULL THEN

5209: 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);
5210: 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);
5211: 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);
5212: END IF;
5213: IF l_contact_contains_str IS NULL THEN
5214: defct := 1;
5215: END IF;
5216: IF l_contact_pt_contains_str IS NULL THEN
5217: defcpt := 1;

Line 5216: IF l_contact_pt_contains_str IS NULL THEN

5212: END IF;
5213: IF l_contact_contains_str IS NULL THEN
5214: defct := 1;
5215: END IF;
5216: IF l_contact_pt_contains_str IS NULL THEN
5217: defcpt := 1;
5218: END IF;
5219:
5220: IF l_contact_contains_str IS NOT NULL THEN

Line 5220: IF l_contact_contains_str IS NOT NULL THEN

5216: IF l_contact_pt_contains_str IS NULL THEN
5217: defcpt := 1;
5218: END IF;
5219:
5220: IF l_contact_contains_str IS NOT NULL THEN
5221: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5222: LOOP
5223: FETCH l_contact_cur INTO
5224: l_org_contact_id, l_ct_party_id ;

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

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

Line 5242: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

Line 5350: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

Line 5420: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

Line 5492: l_party_contains_str VARCHAR2(32000);

5488: x_search_ctx_id IN OUT NUMBER
5489: ) IS
5490:
5491: -- Strings to hold the generated Intermedia query strings
5492: l_party_contains_str VARCHAR2(32000);
5493: l_party_site_contains_str VARCHAR2(32000);
5494: l_contact_contains_str VARCHAR2(32000);
5495: l_contact_pt_contains_str VARCHAR2(32000);
5496: l_tmp VARCHAR2(32000);

Line 5493: l_party_site_contains_str VARCHAR2(32000);

5489: ) IS
5490:
5491: -- Strings to hold the generated Intermedia query strings
5492: l_party_contains_str VARCHAR2(32000);
5493: l_party_site_contains_str VARCHAR2(32000);
5494: l_contact_contains_str VARCHAR2(32000);
5495: l_contact_pt_contains_str VARCHAR2(32000);
5496: l_tmp VARCHAR2(32000);
5497:

Line 5494: l_contact_contains_str VARCHAR2(32000);

5490:
5491: -- Strings to hold the generated Intermedia query strings
5492: l_party_contains_str VARCHAR2(32000);
5493: l_party_site_contains_str VARCHAR2(32000);
5494: l_contact_contains_str VARCHAR2(32000);
5495: l_contact_pt_contains_str VARCHAR2(32000);
5496: l_tmp VARCHAR2(32000);
5497:
5498: -- Other local variables

Line 5495: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

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

Line 5598: IF l_party_site_contains_str IS NOT NULL THEN

5594: 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);
5595: END LOOP;
5596: CLOSE l_party_cur;
5597:
5598: IF l_party_site_contains_str IS NOT NULL THEN
5599: l_max_score := 0;
5600: l_max_id := 0;
5601: l_max_idx := 0;
5602: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5599: l_max_score := 0;
5600: l_max_id := 0;
5601: l_max_idx := 0;
5602: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5603: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5604: ELSE
5605: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5606: END IF;
5607: LOOP

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

5601: l_max_idx := 0;
5602: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5603: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5604: ELSE
5605: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5606: END IF;
5607: LOOP
5608: FETCH l_party_site_cur INTO
5609: 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 5630: IF l_contact_contains_str IS NOT NULL THEN

5626: 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);
5627: END IF;
5628: END IF;
5629:
5630: IF l_contact_contains_str IS NOT NULL THEN
5631: l_max_score := 0;
5632: l_max_id := 0;
5633: l_max_idx := 0;
5634: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5630: IF l_contact_contains_str IS NOT NULL THEN
5631: l_max_score := 0;
5632: l_max_id := 0;
5633: l_max_idx := 0;
5634: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5635: LOOP
5636: FETCH l_contact_cur INTO
5637: l_org_contact_id, l_ct_party_id ;
5638: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5652: IF l_contact_pt_contains_str IS NOT NULL THEN

5648: 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);
5649: END IF;
5650: END IF;
5651:
5652: IF l_contact_pt_contains_str IS NOT NULL THEN
5653: l_max_score := 0;
5654: l_max_id := 0;
5655: l_max_idx := 0;
5656: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5653: l_max_score := 0;
5654: l_max_id := 0;
5655: l_max_idx := 0;
5656: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5657: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5658: ELSE
5659: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5660: END IF;
5661: LOOP

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

5655: l_max_idx := 0;
5656: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5657: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5658: ELSE
5659: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5660: END IF;
5661: LOOP
5662: FETCH l_contact_pt_cur INTO
5663: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;