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_48 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 1065: l_party_contains_str VARCHAR2(32000);

1061: p_party_max_score NUMBER,
1062: p_denorm_max_score NUMBER,
1063: p_non_denorm_max_score NUMBER,
1064: p_threshold NUMBER) RETURN VARCHAR2 IS
1065: l_party_contains_str VARCHAR2(32000);
1066: l_party_filter VARCHAR2(1) := null;
1067: l_prim_temp VARCHAR2(4000);
1068: l_denorm_str VARCHAR2(4000);
1069: BEGIN

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

1105:
1106: IF l_denorm_str IS NOT NULL THEN
1107: IF (p_non_denorm_max_score+10)>=p_threshold THEN
1108: l_denorm_str := NULL;
1109: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1110: ELSIF (p_non_denorm_max_score+p_denorm_max_score+10)>=p_threshold THEN
1111: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1112: l_denorm_str := NULL;
1113: END IF;

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

1107: IF (p_non_denorm_max_score+10)>=p_threshold THEN
1108: l_denorm_str := NULL;
1109: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1110: ELSIF (p_non_denorm_max_score+p_denorm_max_score+10)>=p_threshold THEN
1111: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1112: l_denorm_str := NULL;
1113: END IF;
1114: ELSE
1115: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1111: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1112: l_denorm_str := NULL;
1113: END IF;
1114: ELSE
1115: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1116: END IF;
1117:
1118: -- Setup query string for SIC_CODE
1119: l_prim_temp := null;

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+20)>=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+20)>=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+20)>=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+20)>=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 PARTY_NAME
1136: l_prim_temp := null;

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

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

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

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

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

1147: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1148: l_denorm_str := NULL;
1149: END IF;
1150: ELSE
1151: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1152: END IF;
1153:
1154: -- Setup query string for PARTY_NUMBER
1155: 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+100)>=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+100)>=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+100)>=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+100)>=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: IF lengthb(l_party_contains_str) > 4000 THEN
1171: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1172: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 1170: IF lengthb(l_party_contains_str) > 4000 THEN

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: IF lengthb(l_party_contains_str) > 4000 THEN
1171: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1172: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1173: FND_MSG_PUB.ADD;
1174: RAISE FND_API.G_EXC_ERROR;

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

1172: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1173: FND_MSG_PUB.ADD;
1174: RAISE FND_API.G_EXC_ERROR;
1175: END IF;
1176: IF (p_match_str = ' AND ' OR p_party_max_score 1177: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1178: 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);
1179: END IF;
1180: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

1174: RAISE FND_API.G_EXC_ERROR;
1175: END IF;
1176: IF (p_match_str = ' AND ' OR p_party_max_score 1177: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1178: 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);
1179: END IF;
1180: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1181: 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);
1182: END IF;

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

1177: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1178: 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);
1179: END IF;
1180: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1181: 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);
1182: END IF;
1183: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1184: 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);
1185: END IF;

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

1182: END IF;
1183: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1184: 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);
1185: END IF;
1186: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1187: ELSE
1188: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1189: 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);
1190: 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 1189: 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);

1185: END IF;
1186: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1187: ELSE
1188: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1189: 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);
1190: 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);
1191: END IF;
1192: RETURN l_party_contains_str;
1193: END IF;

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

1186: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1187: ELSE
1188: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1189: 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);
1190: 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);
1191: END IF;
1192: RETURN l_party_contains_str;
1193: END IF;
1194: END;

Line 1192: RETURN l_party_contains_str;

1188: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1189: 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);
1190: 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);
1191: END IF;
1192: RETURN l_party_contains_str;
1193: END IF;
1194: END;
1195: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1196: l_contains_str VARCHAR2(32000);

Line 1196: l_contains_str VARCHAR2(32000);

1192: RETURN l_party_contains_str;
1193: END IF;
1194: END;
1195: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1196: l_contains_str VARCHAR2(32000);
1197: l_contains_str_temp VARCHAR2(32000);
1198: l_den_contains_str VARCHAR2(32000);
1199: l_den_contains_str_temp VARCHAR2(32000);
1200: l_filter_str VARCHAR2(4000) := null;

Line 1197: l_contains_str_temp VARCHAR2(32000);

1193: END IF;
1194: END;
1195: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1196: l_contains_str VARCHAR2(32000);
1197: l_contains_str_temp VARCHAR2(32000);
1198: l_den_contains_str VARCHAR2(32000);
1199: l_den_contains_str_temp VARCHAR2(32000);
1200: l_filter_str VARCHAR2(4000) := null;
1201: l_prim_temp VARCHAR2(4000) := null;

Line 1198: l_den_contains_str VARCHAR2(32000);

1194: END;
1195: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1196: l_contains_str VARCHAR2(32000);
1197: l_contains_str_temp VARCHAR2(32000);
1198: l_den_contains_str VARCHAR2(32000);
1199: l_den_contains_str_temp VARCHAR2(32000);
1200: l_filter_str VARCHAR2(4000) := null;
1201: l_prim_temp VARCHAR2(4000) := null;
1202: l_prim_temp_den VARCHAR2(4000) := null;

Line 1199: l_den_contains_str_temp VARCHAR2(32000);

1195: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1196: l_contains_str VARCHAR2(32000);
1197: l_contains_str_temp VARCHAR2(32000);
1198: l_den_contains_str VARCHAR2(32000);
1199: l_den_contains_str_temp VARCHAR2(32000);
1200: l_filter_str VARCHAR2(4000) := null;
1201: l_prim_temp VARCHAR2(4000) := null;
1202: l_prim_temp_den VARCHAR2(4000) := null;
1203: BEGIN

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

1205: 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);
1206: END IF;
1207: x_denorm_str := NULL;
1208:
1209: -- Dynamic setup of party site contains str
1210: --
1211: -- For each primary transformation add to intermedia query if it
1212: -- is not null
1213: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 1214: l_contains_str_temp := null;

1210: --
1211: -- For each primary transformation add to intermedia query if it
1212: -- is not null
1213: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1214: l_contains_str_temp := null;
1215: l_den_contains_str_temp := null;
1216: l_filter_str := null;
1217:
1218: -- Setup query string for ADDRESS

Line 1215: l_den_contains_str_temp := null;

1211: -- For each primary transformation add to intermedia query if it
1212: -- is not null
1213: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1214: l_contains_str_temp := null;
1215: l_den_contains_str_temp := null;
1216: l_filter_str := null;
1217:
1218: -- Setup query string for ADDRESS
1219: l_prim_temp := null;

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

1219: l_prim_temp := null;
1220: l_prim_temp_den := null;
1221: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1222: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1223: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1224:
1225: -- Setup query string for CITY
1226: l_prim_temp := null;
1227: l_prim_temp_den := null;

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

1228: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1229: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1230: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1231: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1232: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1233: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1234:
1235: -- Setup query string for STATE
1236: l_prim_temp := null;

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

1229: g_party_site_stage_list(I).TX10,'A29',l_prim_temp);
1230: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE
1231: g_party_site_stage_list(I).TX10,'D_PS',l_prim_temp_den);
1232: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1233: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1234:
1235: -- Setup query string for STATE
1236: l_prim_temp := null;
1237: l_prim_temp_den := null;

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

1238: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1239: g_party_site_stage_list(I).TX14,'A32',l_prim_temp);
1240: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1241: g_party_site_stage_list(I).TX14,'D_PS',l_prim_temp_den);
1242: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1243: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1244:
1245: -- Setup query string for COUNTRY
1246: l_prim_temp := null;

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

1239: g_party_site_stage_list(I).TX14,'A32',l_prim_temp);
1240: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT + WRSTATE
1241: g_party_site_stage_list(I).TX14,'D_PS',l_prim_temp_den);
1242: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1243: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1244:
1245: -- Setup query string for COUNTRY
1246: l_prim_temp := null;
1247: l_prim_temp_den := null;

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

1248: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1249: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1250: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1251: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1252: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1253: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1254:
1255: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1256: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

1249: g_party_site_stage_list(I).TX22,'A36',l_prim_temp);
1250: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT
1251: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1252: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1253: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1254:
1255: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1256: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1257: END LOOP;

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

1251: g_party_site_stage_list(I).TX22,'D_PS',l_prim_temp_den);
1252: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1253: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1254:
1255: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1256: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1257: END LOOP;
1258: -- Add the search criteria to query string
1259: IF lengthb(l_contains_str) > 4000 THEN

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

1252: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1253: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1254:
1255: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1256: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1257: END LOOP;
1258: -- Add the search criteria to query string
1259: IF lengthb(l_contains_str) > 4000 THEN
1260: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1259: IF lengthb(l_contains_str) > 4000 THEN

1255: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1256: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1257: END LOOP;
1258: -- Add the search criteria to query string
1259: IF lengthb(l_contains_str) > 4000 THEN
1260: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1261: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1262: FND_MSG_PUB.ADD;
1263: RAISE FND_API.G_EXC_ERROR;

Line 1265: x_denorm_str := l_den_contains_str;

1261: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1262: FND_MSG_PUB.ADD;
1263: RAISE FND_API.G_EXC_ERROR;
1264: END IF;
1265: x_denorm_str := l_den_contains_str;
1266: RETURN l_contains_str;
1267: END;
1268:
1269: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1266: RETURN l_contains_str;

1262: FND_MSG_PUB.ADD;
1263: RAISE FND_API.G_EXC_ERROR;
1264: END IF;
1265: x_denorm_str := l_den_contains_str;
1266: RETURN l_contains_str;
1267: END;
1268:
1269: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1270: l_contains_str VARCHAR2(32000);

Line 1270: l_contains_str VARCHAR2(32000);

1266: RETURN l_contains_str;
1267: END;
1268:
1269: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1270: l_contains_str VARCHAR2(32000);
1271: l_contains_str_temp VARCHAR2(32000);
1272: l_den_contains_str VARCHAR2(32000);
1273: l_den_contains_str_temp VARCHAR2(32000);
1274: l_filter_str VARCHAR2(4000) := null;

Line 1271: l_contains_str_temp VARCHAR2(32000);

1267: END;
1268:
1269: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1270: l_contains_str VARCHAR2(32000);
1271: l_contains_str_temp VARCHAR2(32000);
1272: l_den_contains_str VARCHAR2(32000);
1273: l_den_contains_str_temp VARCHAR2(32000);
1274: l_filter_str VARCHAR2(4000) := null;
1275: l_prim_temp VARCHAR2(4000) := null;

Line 1272: l_den_contains_str VARCHAR2(32000);

1268:
1269: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1270: l_contains_str VARCHAR2(32000);
1271: l_contains_str_temp VARCHAR2(32000);
1272: l_den_contains_str VARCHAR2(32000);
1273: l_den_contains_str_temp VARCHAR2(32000);
1274: l_filter_str VARCHAR2(4000) := null;
1275: l_prim_temp VARCHAR2(4000) := null;
1276: l_prim_temp_den VARCHAR2(4000) := null;

Line 1273: l_den_contains_str_temp VARCHAR2(32000);

1269: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1270: l_contains_str VARCHAR2(32000);
1271: l_contains_str_temp VARCHAR2(32000);
1272: l_den_contains_str VARCHAR2(32000);
1273: l_den_contains_str_temp VARCHAR2(32000);
1274: l_filter_str VARCHAR2(4000) := null;
1275: l_prim_temp VARCHAR2(4000) := null;
1276: l_prim_temp_den VARCHAR2(4000) := null;
1277: BEGIN

Line 1286: l_contains_str VARCHAR2(32000);

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

Line 1287: l_contains_str_temp VARCHAR2(32000);

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

Line 1288: l_den_contains_str VARCHAR2(32000);

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

Line 1289: l_den_contains_str_temp VARCHAR2(32000);

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

Line 1306: p_party_contains_str VARCHAR2,

1302: p_dup_party_id NUMBER,
1303: p_restrict_sql VARCHAR2,
1304: p_match_str VARCHAR2,
1305: p_search_merged VARCHAR2,
1306: p_party_contains_str VARCHAR2,
1307: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1308: l_sqlstr VARCHAR2(4000);
1309: l_search_merged VARCHAR2(1);
1310: BEGIN

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

1319: END IF;
1320: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1321: 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);
1322: END IF;
1323: 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
1324: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1325: 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);
1326: END IF;
1327: OPEN x_cursor FOR

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

1357: l_sqlstr := l_sqlstr || ' (TX46 like :TX46||'' %'' ';
1358: l_sqlstr := l_sqlstr || ' )) ';
1359: l_sqlstr := l_sqlstr || ' ) ';
1360: END IF;
1361: 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
1362: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1363: 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);
1364: END IF;
1365: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

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

1359: l_sqlstr := l_sqlstr || ' ) ';
1360: END IF;
1361: 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
1362: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1363: 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);
1364: END IF;
1365: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1366: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX10||'' %'' ';
1367: l_sqlstr := l_sqlstr || ' )) ';

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

1386: END IF;
1387: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1388: 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);
1389: END IF;
1390: 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
1391: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1392: 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);
1393: END IF;
1394: 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 1392: 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);

1388: 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);
1389: END IF;
1390: 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
1391: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1392: 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);
1393: END IF;
1394: 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
1395: ,g_party_stage_rec.TX34
1396: ,g_party_stage_rec.TX34

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

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

1418: ,g_party_stage_rec.TX46
1419: ;
1420: 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
1421: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1422: 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);
1423: END IF;
1424: 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
1425: ,g_party_site_stage_list(1).TX10
1426: ,g_party_site_stage_list(1).TX10

Line 1443: p_contains_str VARCHAR2,

1439: END;
1440: PROCEDURE open_party_cursor(
1441: p_dup_party_id NUMBER,
1442: p_restrict_sql VARCHAR2,
1443: p_contains_str VARCHAR2,
1444: p_search_ctx_id NUMBER,
1445: p_match_str VARCHAR2,
1446: p_search_merged VARCHAR2,
1447: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1462: IF p_contains_str IS NULL THEN

1458: l_search_merged := 'N';
1459: ELSE
1460: l_search_merged := p_search_merged;
1461: END IF;
1462: IF p_contains_str IS NULL THEN
1463: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1464: 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);
1465: END IF;
1466: OPEN x_cursor FOR

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

1460: l_search_merged := p_search_merged;
1461: END IF;
1462: IF p_contains_str IS NULL THEN
1463: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1464: 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);
1465: END IF;
1466: OPEN x_cursor FOR
1467: SELECT PARTY_ID , TX4, TX8, TX19, TX34, TX36, TX46
1468: FROM HZ_STAGED_PARTIES stage

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

1477: END IF;
1478: OPEN x_cursor FOR
1479: SELECT PARTY_ID , TX4, TX8, TX19, TX34, TX36, TX46
1480: FROM HZ_STAGED_PARTIES stage
1481: WHERE contains( concat_col, p_contains_str)>0
1482: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1483: AND( (l_search_merged ='Y' )
1484: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1485: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1490: END IF;
1491: OPEN x_cursor FOR
1492: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX4, TX8, TX19, TX34, TX36, TX46
1493: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1494: WHERE contains( concat_col, p_contains_str)>0
1495: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1496: AND d.party_id = stage.party_id
1497: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1498: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1513: END IF;
1514: IF p_search_ctx_id IS NULL THEN
1515: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX4, TX8, TX19, TX34, TX36, TX46'||
1516: ' FROM HZ_STAGED_PARTIES stage'||
1517: ' WHERE contains( concat_col, :cont)>0'||
1518: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1519: ' AND ('||p_restrict_sql||')' ||
1520: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1521: IF l_search_merged = 'Y' THEN

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

1525: ELSE
1526: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1527: END IF;
1528: output_long_strings('----------------------------------------------------------');
1529: output_long_strings('Party Contains String = '||p_contains_str);
1530: output_long_strings('Restrict Sql = '||p_restrict_sql);
1531: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1532: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1533: ,p_dup_party_id, p_dup_party_id;

Line 1531: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1527: END IF;
1528: output_long_strings('----------------------------------------------------------');
1529: output_long_strings('Party Contains String = '||p_contains_str);
1530: output_long_strings('Restrict Sql = '||p_restrict_sql);
1531: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1532: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1533: ,p_dup_party_id, p_dup_party_id;
1534: END IF;
1535: END IF;

Line 1562: p_contains_str VARCHAR2,

1558: PROCEDURE open_party_site_cursor(
1559: p_dup_party_id NUMBER,
1560: p_party_id NUMBER,
1561: p_restrict_sql VARCHAR2,
1562: p_contains_str VARCHAR2,
1563: p_search_ctx_id NUMBER,
1564: p_search_merged VARCHAR2,
1565: p_search_rel_sites VARCHAR2,
1566: p_person_api VARCHAR2,

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

1590: IF p_search_rel_sites = 'N' THEN
1591: OPEN x_cursor FOR
1592: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX10, TX14, TX22
1593: FROM HZ_STAGED_PARTY_SITES stage
1594: WHERE contains( concat_col, p_contains_str)>0
1595: AND( (l_search_merged ='Y' )
1596: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1597: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1598: AND stage.party_id = p_party_id;

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

1599: ELSE
1600: OPEN x_cursor FOR
1601: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX10, TX14, TX22
1602: FROM HZ_STAGED_PARTY_SITES stage
1603: WHERE contains( concat_col, p_contains_str)>0
1604: AND( (l_search_merged ='Y' )
1605: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1606: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1607: AND stage.party_id = p_party_id

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

1607: AND stage.party_id = p_party_id
1608: UNION
1609: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX4, TX10, TX14, TX22
1610: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1611: WHERE contains( concat_col, p_contains_str)>0
1612: AND( (l_search_merged ='Y' )
1613: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1614: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1615: AND r.object_id = p_party_id

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

1629: END IF;
1630: OPEN x_cursor FOR
1631: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX10, TX14, TX22
1632: FROM HZ_STAGED_PARTY_SITES stage
1633: WHERE contains( concat_col, p_contains_str)>0
1634: AND EXISTS (
1635: SELECT 1 FROM HZ_STAGED_PARTIES p
1636: WHERE p.PARTY_ID = stage.PARTY_ID
1637: AND( (l_search_merged ='Y' )

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

1649: IF p_person_api = 'Y' THEN
1650: OPEN x_cursor FOR
1651: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX10, TX14, TX22
1652: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1653: WHERE contains( concat_col, p_contains_str)>0
1654: AND( (l_search_merged ='Y' )
1655: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1656: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1657: AND d.search_context_id = p_search_ctx_id

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

1659: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1660: UNION
1661: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX4, TX10, TX14, TX22
1662: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1663: WHERE contains( concat_col, p_contains_str)>0
1664: AND( (l_search_merged ='Y' )
1665: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1666: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1667: AND d.search_context_id = p_search_ctx_id

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

1672: ELSE
1673: OPEN x_cursor FOR
1674: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX10, TX14, TX22
1675: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1676: WHERE contains( concat_col, p_contains_str)>0
1677: AND( (l_search_merged ='Y' )
1678: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1679: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1680: AND d.search_context_id = p_search_ctx_id

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

1717: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1718: END IF;
1719: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX10, TX14, TX22'||
1720: ' FROM HZ_STAGED_PARTY_SITES stage'||
1721: ' WHERE contains( concat_col, :cont)>0'||
1722: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1723: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1724: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1725: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1727: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1728: ' UNION ' ||
1729: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX10, TX14, TX22'||
1730: ' FROM HZ_STAGED_PARTY_SITES stage'||
1731: ' WHERE contains( concat_col, :cont)>0'||
1732: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1733: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1734: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1735: ' AND ORG_CONTACT_ID IN ' ||

Line 1741: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1737: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1738: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1739: ' and ('||p_restrict_sql1|| ') )' ||
1740: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1741: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1742: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1743: ELSE
1744: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX10, TX14, TX22'||
1745: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1742: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1738: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1739: ' and ('||p_restrict_sql1|| ') )' ||
1740: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1741: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1742: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1743: ELSE
1744: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX10, TX14, TX22'||
1745: ' FROM HZ_STAGED_PARTY_SITES stage'||
1746: ' WHERE contains( concat_col, :cont)>0'||

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

1742: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1743: ELSE
1744: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX10, TX14, TX22'||
1745: ' FROM HZ_STAGED_PARTY_SITES stage'||
1746: ' WHERE contains( concat_col, :cont)>0'||
1747: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1748: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1749: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1750: ' AND EXISTS ('||

Line 1756: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1752: ' WHERE p.party_id = stage.party_id ' ||
1753: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1754: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1755: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1756: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1757: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1758: ,p_dup_party_id, p_dup_party_id;
1759: END IF;
1760: ELSE

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

1759: END IF;
1760: ELSE
1761: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX10, TX14, TX22'||
1762: ' FROM HZ_STAGED_PARTY_SITES stage'||
1763: ' WHERE contains( concat_col, :cont)>0'||
1764: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1765: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1766: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1767: ' AND EXISTS ('||

Line 1773: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1769: ' WHERE p.party_id = stage.party_id ' ||
1770: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1771: ' AND ('||p_restrict_sql||')' ||
1772: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1773: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1774: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1775: ,p_dup_party_id, p_dup_party_id;
1776: END IF;
1777: END IF;

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

1775: ,p_dup_party_id, p_dup_party_id;
1776: END IF;
1777: END IF;
1778: output_long_strings('----------------------------------------------------------');
1779: output_long_strings('Party Site Contains String = '||p_contains_str);
1780: output_long_strings('Restrict Sql = '||p_restrict_sql);
1781: exception
1782: when others then
1783: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1807: p_contains_str VARCHAR2,

1803: PROCEDURE open_contact_cursor(
1804: p_dup_party_id NUMBER,
1805: p_party_id NUMBER,
1806: p_restrict_sql VARCHAR2,
1807: p_contains_str VARCHAR2,
1808: p_search_ctx_id NUMBER,
1809: p_search_merged VARCHAR2,
1810: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1811: l_sqlstr VARCHAR2(4000);

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

1831: END IF;
1832: OPEN x_cursor FOR
1833: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1834: FROM HZ_STAGED_CONTACTS stage
1835: WHERE contains( concat_col, p_contains_str)>0
1836: AND EXISTS (
1837: SELECT 1 FROM HZ_STAGED_PARTIES p
1838: WHERE p.PARTY_ID = stage.PARTY_ID
1839: AND( (l_search_merged ='Y' )

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

1854: END IF;
1855: OPEN x_cursor FOR
1856: SELECT ORG_CONTACT_ID, PARTY_ID
1857: FROM HZ_STAGED_CONTACTS stage
1858: WHERE contains( concat_col, p_contains_str)>0
1859: AND EXISTS (
1860: SELECT 1 FROM HZ_STAGED_PARTIES p
1861: WHERE p.PARTY_ID = stage.PARTY_ID
1862: AND( (l_search_merged ='Y' )

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

1873: END IF;
1874: OPEN x_cursor FOR
1875: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1876: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1877: WHERE contains( concat_col, p_contains_str)>0
1878: AND d.search_context_id = p_search_ctx_id
1879: AND d.party_id = stage.party_id
1880: AND( (l_search_merged ='Y' )
1881: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1907: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1908: END IF;
1909: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1910: ' FROM HZ_STAGED_CONTACTS stage'||
1911: ' WHERE contains( concat_col, :cont)>0'||
1912: ' AND EXISTS ('||
1913: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1914: ' WHERE p.party_id = stage.party_id ' ||
1915: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1921: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1917: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1918: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1919: ' AND ('||p_restrict_sql||')' ||
1920: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1921: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1922: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1923: ,p_dup_party_id, p_dup_party_id;
1924: END IF;
1925: output_long_strings('----------------------------------------------------------');

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

1922: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1923: ,p_dup_party_id, p_dup_party_id;
1924: END IF;
1925: output_long_strings('----------------------------------------------------------');
1926: output_long_strings('Contacts Contains String = '||p_contains_str);
1927: output_long_strings('Restrict Sql = '||p_restrict_sql);
1928: exception
1929: when others then
1930: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1954: p_contains_str VARCHAR2,

1950: PROCEDURE open_contact_pt_cursor(
1951: p_dup_party_id NUMBER,
1952: p_party_id NUMBER,
1953: p_restrict_sql VARCHAR2,
1954: p_contains_str VARCHAR2,
1955: p_search_ctx_id NUMBER,
1956: p_search_merged VARCHAR2,
1957: p_search_rel_cpts VARCHAR2,
1958: p_person_api VARCHAR2,

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

1980: THEN
1981: OPEN x_cursor FOR
1982: 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
1983: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1984: WHERE contains( concat_col, p_contains_str)>0
1985: AND d.search_context_id = p_search_ctx_id
1986: AND( (l_search_merged ='Y' )
1987: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1988: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1992: THEN
1993: OPEN x_cursor FOR
1994: 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
1995: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1996: WHERE contains( concat_col, p_contains_str)>0
1997: AND d.search_context_id = p_search_ctx_id
1998: AND( (l_search_merged ='Y' )
1999: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2000: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2009: IF p_search_rel_cpts = 'N' THEN
2010: OPEN x_cursor FOR
2011: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2012: FROM HZ_STAGED_CONTACT_POINTS stage
2013: WHERE contains( concat_col, p_contains_str)>0
2014: AND( (l_search_merged ='Y' )
2015: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2016: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2017: AND stage.party_id = p_party_id;

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

2018: ELSE
2019: OPEN x_cursor FOR
2020: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2021: FROM HZ_STAGED_CONTACT_POINTS stage
2022: WHERE contains( concat_col, p_contains_str)>0
2023: AND( (l_search_merged ='Y' )
2024: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2025: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2026: AND stage.party_id = p_party_id

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

2026: AND stage.party_id = p_party_id
2027: UNION
2028: 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
2029: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2030: WHERE contains( concat_col, p_contains_str)>0
2031: AND r.object_id = p_party_id
2032: AND( (l_search_merged ='Y' )
2033: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2034: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2048: END IF;
2049: OPEN x_cursor FOR
2050: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
2051: FROM HZ_STAGED_CONTACT_POINTS stage
2052: WHERE contains( concat_col, p_contains_str)>0
2053: AND EXISTS (
2054: SELECT 1 FROM HZ_STAGED_PARTIES p
2055: WHERE p.PARTY_ID = stage.PARTY_ID
2056: AND( (l_search_merged ='Y' )

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

2068: IF p_person_api = 'Y' THEN
2069: OPEN x_cursor FOR
2070: 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
2071: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2072: WHERE contains( concat_col, p_contains_str)>0
2073: AND d.search_context_id = p_search_ctx_id
2074: AND d.party_id = stage.party_id
2075: AND( (l_search_merged ='Y' )
2076: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2078: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2079: UNION
2080: 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
2081: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2082: WHERE contains( concat_col, p_contains_str)>0
2083: AND d.search_context_id = p_search_ctx_id
2084: AND d.party_id = r.subject_id
2085: AND r.relationship_id = oc.party_relationship_id
2086: AND oc.org_contact_id = stage.org_contact_id

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

2091: ELSE
2092: OPEN x_cursor FOR
2093: 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
2094: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2095: WHERE contains( concat_col, p_contains_str)>0
2096: AND d.search_context_id = p_search_ctx_id
2097: AND d.party_id = stage.party_id
2098: AND( (l_search_merged ='Y' )
2099: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2136: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2137: END IF;
2138: 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 '||
2139: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2140: ' WHERE contains( concat_col, :cont)>0 '||
2141: ' AND (stage.org_contact_id is null '||
2142: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2143: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2144: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

2146: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2147: ' UNION ' ||
2148: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2149: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2150: ' WHERE contains( concat_col, :cont)>0 '||
2151: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2152: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2153: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2154: ' AND (stage.org_contact_id in ' ||

Line 2160: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

2156: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2157: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2158: ' and ('||p_restrict_sql1 || ') ) )' ||
2159: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2160: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2161: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2162: ELSE
2163: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2164: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 2161: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

2157: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2158: ' and ('||p_restrict_sql1 || ') ) )' ||
2159: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2160: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2161: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2162: ELSE
2163: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2164: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2165: ' WHERE contains( concat_col, :cont)>0'||

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

2161: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2162: ELSE
2163: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2164: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2165: ' WHERE contains( concat_col, :cont)>0'||
2166: ' AND EXISTS ('||
2167: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2168: ' WHERE p.party_id = stage.party_id ' ||
2169: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2175: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2171: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2172: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2173: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2174: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2175: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2176: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2177: ,p_dup_party_id, p_dup_party_id;
2178: END IF;
2179: ELSE

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

2178: END IF;
2179: ELSE
2180: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
2181: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2182: ' WHERE contains( concat_col, :cont)>0'||
2183: ' AND EXISTS ('||
2184: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2185: ' WHERE p.party_id = stage.party_id ' ||
2186: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2192: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2188: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2189: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2190: ' AND ('||p_restrict_sql||')' ||
2191: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2192: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2193: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2194: ,p_dup_party_id, p_dup_party_id;
2195: END IF;
2196: END IF;

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

2195: END IF;
2196: END IF;
2197: END IF;
2198: output_long_strings('----------------------------------------------------------');
2199: output_long_strings('Contact Points Contains String = '||p_contains_str);
2200: output_long_strings('Restrict Sql = '||p_restrict_sql);
2201: exception
2202: when others then
2203: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2320: l_party_contains_str VARCHAR2(32000);

2316: x_num_matches OUT NUMBER
2317: ) IS
2318:
2319: -- Strings to hold the generated Intermedia query strings
2320: l_party_contains_str VARCHAR2(32000);
2321: l_party_site_contains_str VARCHAR2(32000);
2322: l_contact_contains_str VARCHAR2(32000);
2323: l_contact_pt_contains_str VARCHAR2(32000);
2324: l_denorm_str VARCHAR2(32000);

Line 2321: l_party_site_contains_str VARCHAR2(32000);

2317: ) IS
2318:
2319: -- Strings to hold the generated Intermedia query strings
2320: l_party_contains_str VARCHAR2(32000);
2321: l_party_site_contains_str VARCHAR2(32000);
2322: l_contact_contains_str VARCHAR2(32000);
2323: l_contact_pt_contains_str VARCHAR2(32000);
2324: l_denorm_str VARCHAR2(32000);
2325: l_ps_denorm_str VARCHAR2(32000);

Line 2322: l_contact_contains_str VARCHAR2(32000);

2318:
2319: -- Strings to hold the generated Intermedia query strings
2320: l_party_contains_str VARCHAR2(32000);
2321: l_party_site_contains_str VARCHAR2(32000);
2322: l_contact_contains_str VARCHAR2(32000);
2323: l_contact_pt_contains_str VARCHAR2(32000);
2324: l_denorm_str VARCHAR2(32000);
2325: l_ps_denorm_str VARCHAR2(32000);
2326: l_ct_denorm_str VARCHAR2(32000);

Line 2323: l_contact_pt_contains_str VARCHAR2(32000);

2319: -- Strings to hold the generated Intermedia query strings
2320: l_party_contains_str VARCHAR2(32000);
2321: l_party_site_contains_str VARCHAR2(32000);
2322: l_contact_contains_str VARCHAR2(32000);
2323: l_contact_pt_contains_str VARCHAR2(32000);
2324: l_denorm_str VARCHAR2(32000);
2325: l_ps_denorm_str VARCHAR2(32000);
2326: l_ct_denorm_str VARCHAR2(32000);
2327: l_cpt_denorm_str VARCHAR2(32000);

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

2413: call_type(2) := 'OR';
2414: END;
2415:
2416: /** Private procedure to acquire and score at party level ***/
2417: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2418: l_party_id_idx NUMBER:=1;
2419: l_ctx_id NUMBER;
2420: l_precalc_score BOOLEAN := FALSE;
2421: BEGIN

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

2444: l_search_ctx_id,H_PARTY_ID(I));
2445: l_party_id_idx := H_PARTY_ID.COUNT+1;
2446: ELSE
2447: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2448: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2449: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2450: 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);
2451: END IF;
2452: 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 2456: IF (l_party_site_contains_str IS NULL AND

2452: 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
2453: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2454: 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);
2455: END IF;
2456: IF (l_party_site_contains_str IS NULL AND
2457: l_contact_contains_str IS NULL AND
2458: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2459: 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);
2460: RAISE L_RETURN_IMM_EXC;

Line 2457: l_contact_contains_str IS NULL AND

2453: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2454: 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);
2455: END IF;
2456: IF (l_party_site_contains_str IS NULL AND
2457: l_contact_contains_str IS NULL AND
2458: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2459: 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);
2460: RAISE L_RETURN_IMM_EXC;
2461: ELSE

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

2454: 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);
2455: END IF;
2456: IF (l_party_site_contains_str IS NULL AND
2457: l_contact_contains_str IS NULL AND
2458: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2459: 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);
2460: RAISE L_RETURN_IMM_EXC;
2461: ELSE
2462: 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 2462: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2458: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2459: 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);
2460: RAISE L_RETURN_IMM_EXC;
2461: ELSE
2462: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2463: END IF;
2464: ELSE
2465: IF p_index>1 THEN
2466: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2481: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2482: 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);
2483: END IF;
2484: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2485: 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);
2486: END IF;
2487: LOOP
2488: FETCH l_party_cur INTO
2489: l_party_id , l_TX4, l_TX8, l_TX19, l_TX34, l_TX36, l_TX46;

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

2503: l_party_id_idx:= l_party_id_idx+1;
2504: END IF;
2505: IF (l_party_id_idx-1)>l_max_thresh THEN
2506: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2507: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2508: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2509: AND l_contact_pt_contains_str IS NULL THEN
2510: EXIT;
2511: END IF;

Line 2509: AND l_contact_pt_contains_str IS NULL THEN

2505: IF (l_party_id_idx-1)>l_max_thresh THEN
2506: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2507: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2508: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2509: AND l_contact_pt_contains_str IS NULL THEN
2510: EXIT;
2511: END IF;
2512: CLOSE l_party_cur;
2513: IF p_index>1 THEN

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

2534: ROLLBACK to eval_start;
2535: END;
2536:
2537: /** Private procedure to acquire and score at party site level ***/
2538: 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
2539: l_party_id_idx NUMBER:=1;
2540: l_ctx_id NUMBER;
2541: h_ps_id HZ_PARTY_SEARCH.IDList;
2542: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2546: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2547: 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);
2548: 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);
2549: END IF;
2550: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2551: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2552: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2553: l_party_id := H_SCORES.FIRST;
2554: WHILE l_party_id IS NOT NULL LOOP

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

2547: 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);
2548: 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);
2549: END IF;
2550: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2551: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2552: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2553: l_party_id := H_SCORES.FIRST;
2554: WHILE l_party_id IS NOT NULL LOOP
2555: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2548: 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);
2549: END IF;
2550: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2551: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2552: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2553: l_party_id := H_SCORES.FIRST;
2554: WHILE l_party_id IS NOT NULL LOOP
2555: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2556: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2578: l_search_ctx_id,H_PARTY_ID(I));
2579: l_party_id_idx := H_PARTY_ID.COUNT+1;
2580: ELSE
2581: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2582: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2583: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2584: 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);
2585: END IF;
2586: IF p_index>1 THEN

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

2600: END IF;
2601: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2602: 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);
2603: END IF;
2604: 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);
2605: LOOP
2606: FETCH l_party_site_cur INTO
2607: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX10, l_TX14, l_TX22;
2608: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2656: END IF;
2657: END;
2658:
2659: /** Private procedure to acquire and score at contact point level ***/
2660: 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
2661: l_party_id_idx NUMBER:=1;
2662: l_ctx_id NUMBER;
2663: h_ct_id HZ_PARTY_SEARCH.IDList;
2664: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2690: l_search_ctx_id,H_PARTY_ID(I));
2691: l_party_id_idx := H_PARTY_ID.COUNT+1;
2692: ELSE
2693: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2694: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2695: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2696: 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);
2697: END IF;
2698: IF p_index>1 THEN

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

2712: END IF;
2713: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2714: 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);
2715: END IF;
2716: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2717: LOOP
2718: FETCH l_contact_cur INTO
2719: l_org_contact_id, l_ct_party_id ;
2720: EXIT WHEN l_contact_cur%NOTFOUND;

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

2765: END IF;
2766: END;
2767:
2768: /** Private procedure to acquire and score at contact point level ***/
2769: 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
2770: l_party_id_idx NUMBER:=1;
2771: l_ctx_id NUMBER;
2772: h_cpt_id HZ_PARTY_SEARCH.IDList;
2773: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2801: l_search_ctx_id,H_PARTY_ID(I));
2802: l_party_id_idx := H_PARTY_ID.COUNT+1;
2803: ELSE
2804: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2805: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2806: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2807: 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);
2808: END IF;
2809: IF p_index>1 THEN

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

2823: END IF;
2824: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2825: 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);
2826: END IF;
2827: 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);
2828: LOOP
2829: FETCH l_contact_pt_cur INTO
2830: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
2831: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2927: IF l_party_contains_str IS NOT NULL THEN

2923: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2924: l_threshold := 100;
2925: other_acq_criteria_exists := TRUE ;
2926: --check if acquisition criteria exists for any other entity
2927: IF l_party_contains_str IS NOT NULL THEN
2928: acq_cnt := 1;
2929: END IF;
2930: IF l_party_site_contains_str IS NOT NULL THEN
2931: acq_cnt := acq_cnt+1;

Line 2930: IF l_party_site_contains_str IS NOT NULL THEN

2926: --check if acquisition criteria exists for any other entity
2927: IF l_party_contains_str IS NOT NULL THEN
2928: acq_cnt := 1;
2929: END IF;
2930: IF l_party_site_contains_str IS NOT NULL THEN
2931: acq_cnt := acq_cnt+1;
2932: END IF;
2933: IF l_contact_contains_str IS NOT NULL THEN
2934: acq_cnt := acq_cnt+1;

Line 2933: IF l_contact_contains_str IS NOT NULL THEN

2929: END IF;
2930: IF l_party_site_contains_str IS NOT NULL THEN
2931: acq_cnt := acq_cnt+1;
2932: END IF;
2933: IF l_contact_contains_str IS NOT NULL THEN
2934: acq_cnt := acq_cnt+1;
2935: END IF;
2936: IF l_contact_pt_contains_str IS NOT NULL THEN
2937: acq_cnt := acq_cnt+1;

Line 2936: IF l_contact_pt_contains_str IS NOT NULL THEN

2932: END IF;
2933: IF l_contact_contains_str IS NOT NULL THEN
2934: acq_cnt := acq_cnt+1;
2935: END IF;
2936: IF l_contact_pt_contains_str IS NOT NULL THEN
2937: acq_cnt := acq_cnt+1;
2938: END IF;
2939: IF acq_cnt>1 THEN
2940: other_acq_criteria_exists := TRUE;

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

2966: FND_MSG_PUB.ADD;
2967: RAISE FND_API.G_EXC_ERROR;
2968: END IF;
2969: END IF;
2970: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2971: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2972: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2973: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2974: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

2967: RAISE FND_API.G_EXC_ERROR;
2968: END IF;
2969: END IF;
2970: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2971: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2972: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2973: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2974: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2975: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2968: END IF;
2969: END IF;
2970: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2971: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2972: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2973: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2974: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2975: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2976: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

2969: END IF;
2970: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2971: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2972: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2973: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2974: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2975: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2976: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2977: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2970: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2971: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2972: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2973: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2974: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2975: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2976: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2977: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2978: END IF;

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

2971: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2972: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2973: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2974: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2975: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2976: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2977: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2978: END IF;
2979: END;

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

2972: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2973: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2974: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2975: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2976: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2977: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2978: END IF;
2979: END;
2980: /************ End of find_parties private procedures **********/

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

2973: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2974: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2975: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2976: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2977: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2978: END IF;
2979: END;
2980: /************ End of find_parties private procedures **********/
2981:

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

3056: 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);
3057: END IF;
3058: -- ************************************************************
3059: -- STEP 2. Setup of intermedia query strings for Acquisition query
3060: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3061: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3062: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3063: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3064: 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 3061: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

3057: END IF;
3058: -- ************************************************************
3059: -- STEP 2. Setup of intermedia query strings for Acquisition query
3060: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3061: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3062: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3063: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3064: 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);
3065: END IF;

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

3058: -- ************************************************************
3059: -- STEP 2. Setup of intermedia query strings for Acquisition query
3060: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3061: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3062: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3063: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3064: 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);
3065: END IF;
3066: l_denorm_max_score:=0;

Line 3094: 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((100/275)*l_entered_max_score));

3090: END IF;
3091: ELSE
3092: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3093: END IF;
3094: 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((100/275)*l_entered_max_score));
3095: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3096:
3097: -- Setup Search Context ID
3098: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3104: IF l_party_contains_str IS NULL THEN

3100:
3101: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3102: 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);
3103: END IF;
3104: IF l_party_contains_str IS NULL THEN
3105: defpt := 1;
3106: END IF;
3107: IF l_party_site_contains_str IS NULL THEN
3108: defps := 1;

Line 3107: IF l_party_site_contains_str IS NULL THEN

3103: END IF;
3104: IF l_party_contains_str IS NULL THEN
3105: defpt := 1;
3106: END IF;
3107: IF l_party_site_contains_str IS NULL THEN
3108: defps := 1;
3109: END IF;
3110: IF l_contact_contains_str IS NULL THEN
3111: defct := 1;

Line 3110: IF l_contact_contains_str IS NULL THEN

3106: END IF;
3107: IF l_party_site_contains_str IS NULL THEN
3108: defps := 1;
3109: END IF;
3110: IF l_contact_contains_str IS NULL THEN
3111: defct := 1;
3112: END IF;
3113: IF l_contact_pt_contains_str IS NULL THEN
3114: defcpt := 1;

Line 3113: IF l_contact_pt_contains_str IS NULL THEN

3109: END IF;
3110: IF l_contact_contains_str IS NULL THEN
3111: defct := 1;
3112: END IF;
3113: IF l_contact_pt_contains_str IS NULL THEN
3114: defcpt := 1;
3115: END IF;
3116:
3117: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3118: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3119: 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);
3120: END IF;
3121: 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);
3122: 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);
3123: 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);
3124: 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);
3125: 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);
3126: 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 3123: 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);

3119: 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);
3120: END IF;
3121: 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);
3122: 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);
3123: 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);
3124: 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);
3125: 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);
3126: 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);
3127: END IF;

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

3120: END IF;
3121: 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);
3122: 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);
3123: 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);
3124: 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);
3125: 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);
3126: 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);
3127: END IF;
3128: FOR I in 1..5 LOOP

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

3121: 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);
3122: 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);
3123: 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);
3124: 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);
3125: 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);
3126: 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);
3127: END IF;
3128: FOR I in 1..5 LOOP
3129: do_eval(I);

Line 3239: l_party_contains_str VARCHAR2(32000);

3235: x_num_matches OUT NUMBER
3236: ) IS
3237:
3238: -- Strings to hold the generated Intermedia query strings
3239: l_party_contains_str VARCHAR2(32000);
3240: l_party_site_contains_str VARCHAR2(32000);
3241: l_contact_contains_str VARCHAR2(32000);
3242: l_contact_pt_contains_str VARCHAR2(32000);
3243: l_denorm_str VARCHAR2(32000);

Line 3240: l_party_site_contains_str VARCHAR2(32000);

3236: ) IS
3237:
3238: -- Strings to hold the generated Intermedia query strings
3239: l_party_contains_str VARCHAR2(32000);
3240: l_party_site_contains_str VARCHAR2(32000);
3241: l_contact_contains_str VARCHAR2(32000);
3242: l_contact_pt_contains_str VARCHAR2(32000);
3243: l_denorm_str VARCHAR2(32000);
3244: l_ps_denorm_str VARCHAR2(32000);

Line 3241: l_contact_contains_str VARCHAR2(32000);

3237:
3238: -- Strings to hold the generated Intermedia query strings
3239: l_party_contains_str VARCHAR2(32000);
3240: l_party_site_contains_str VARCHAR2(32000);
3241: l_contact_contains_str VARCHAR2(32000);
3242: l_contact_pt_contains_str VARCHAR2(32000);
3243: l_denorm_str VARCHAR2(32000);
3244: l_ps_denorm_str VARCHAR2(32000);
3245: l_ct_denorm_str VARCHAR2(32000);

Line 3242: l_contact_pt_contains_str VARCHAR2(32000);

3238: -- Strings to hold the generated Intermedia query strings
3239: l_party_contains_str VARCHAR2(32000);
3240: l_party_site_contains_str VARCHAR2(32000);
3241: l_contact_contains_str VARCHAR2(32000);
3242: l_contact_pt_contains_str VARCHAR2(32000);
3243: l_denorm_str VARCHAR2(32000);
3244: l_ps_denorm_str VARCHAR2(32000);
3245: l_ct_denorm_str VARCHAR2(32000);
3246: l_cpt_denorm_str VARCHAR2(32000);

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

3362: call_type(2) := 'OR';
3363: END;
3364:
3365: /** Private procedure to acquire and score at party level ***/
3366: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3367: l_party_id_idx NUMBER:=1;
3368: l_ctx_id NUMBER;
3369: BEGIN
3370: SAVEPOINT eval_start;

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

3383: l_search_ctx_id,H_PARTY_ID(I));
3384: l_party_id_idx := H_PARTY_ID.COUNT+1;
3385: ELSE
3386: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3387: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3388: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3389: 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);
3390: END IF;
3391: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3395: IF (l_party_site_contains_str IS NULL AND

3391: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3392: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3393: 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);
3394: END IF;
3395: IF (l_party_site_contains_str IS NULL AND
3396: l_contact_contains_str IS NULL AND
3397: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3398: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3399: RAISE L_RETURN_IMM_EXC;

Line 3396: l_contact_contains_str IS NULL AND

3392: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3393: 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);
3394: END IF;
3395: IF (l_party_site_contains_str IS NULL AND
3396: l_contact_contains_str IS NULL AND
3397: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3398: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3399: RAISE L_RETURN_IMM_EXC;
3400: ELSE

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

3393: 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);
3394: END IF;
3395: IF (l_party_site_contains_str IS NULL AND
3396: l_contact_contains_str IS NULL AND
3397: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3398: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3399: RAISE L_RETURN_IMM_EXC;
3400: ELSE
3401: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3397: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3398: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3399: RAISE L_RETURN_IMM_EXC;
3400: ELSE
3401: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3402: END IF;
3403: ELSE
3404: IF p_index>1 THEN
3405: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3417: l_ctx_id := NULL;
3418: l_party_id_idx := H_PARTY_ID.COUNT+1;
3419: END IF;
3420: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3421: 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);
3422: END IF;
3423: LOOP
3424: FETCH l_party_cur INTO
3425: l_party_id , l_TX4, l_TX8, l_TX19, l_TX34, l_TX36, l_TX46;

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

3439: l_party_id_idx:= l_party_id_idx+1;
3440: END IF;
3441: IF (l_party_id_idx-1)>l_max_thresh THEN
3442: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3443: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3444: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3445: AND l_contact_pt_contains_str IS NULL THEN
3446: H_PARTY_ID.DELETE(l_party_id_idx-1);
3447: H_PARTY_ID_LIST.DELETE(l_index);

Line 3445: AND l_contact_pt_contains_str IS NULL THEN

3441: IF (l_party_id_idx-1)>l_max_thresh THEN
3442: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3443: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3444: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3445: AND l_contact_pt_contains_str IS NULL THEN
3446: H_PARTY_ID.DELETE(l_party_id_idx-1);
3447: H_PARTY_ID_LIST.DELETE(l_index);
3448: H_SCORES.DELETE(l_index);
3449: EXIT;

Line 3474: p_contains_str VARCHAR2,

3470: CLOSE l_party_cur;
3471: ROLLBACK to eval_start;
3472: END;
3473: PROCEDURE open_person_contact_cursor(
3474: p_contains_str VARCHAR2,
3475: p_search_ctx_id NUMBER,
3476: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3477: BEGIN
3478: OPEN x_cursor FOR

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

3477: BEGIN
3478: OPEN x_cursor FOR
3479: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3480: FROM HZ_STAGED_CONTACTS stage
3481: WHERE contains( concat_col, p_contains_str)>0
3482: AND ORG_CONTACT_ID in (
3483: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3484: org_contact_id
3485: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3501: end if;
3502: END;
3503:
3504: /** Private procedure to acquire and score at party site level ***/
3505: 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
3506: l_party_id_idx NUMBER:=1;
3507: l_ctx_id NUMBER;
3508: h_ps_id HZ_PARTY_SEARCH.IDList;
3509: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3529: ELSE
3530: l_party_id_idx := H_PARTY_ID.COUNT+1;
3531: l_ctx_id := NULL;
3532: END IF;
3533: 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);
3534: LOOP
3535: FETCH l_party_site_cur INTO
3536: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX10, l_TX14, l_TX22;
3537: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3600: END IF;
3601: END;
3602:
3603: /** Private procedure to acquire and score at party site level ***/
3604: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3605: l_party_id_idx NUMBER:=1;
3606: l_ctx_id NUMBER;
3607: h_ct_id HZ_PARTY_SEARCH.IDList;
3608: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3616: FORALL I in 1..H_PARTY_ID.COUNT
3617: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3618: l_search_ctx_id,H_PARTY_ID(I));
3619: l_party_id_idx := H_PARTY_ID.COUNT+1;
3620: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3621: LOOP
3622: FETCH l_contact_cur INTO
3623: l_org_contact_id, l_ct_party_id ;
3624: EXIT WHEN l_contact_cur%NOTFOUND;

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

3658: END IF;
3659: END;
3660:
3661: /** Private procedure to acquire and score at contact point level ***/
3662: 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
3663: l_party_id_idx NUMBER:=1;
3664: l_ctx_id NUMBER;
3665: h_cpt_id HZ_PARTY_SEARCH.IDList;
3666: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3703: END IF;
3704: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3705: 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);
3706: END IF;
3707: 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);
3708: LOOP
3709: FETCH l_contact_pt_cur INTO
3710: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3711: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3815: IF l_party_contains_str IS NOT NULL THEN

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

Line 3818: IF l_party_site_contains_str IS NOT NULL THEN

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

Line 3821: IF l_contact_contains_str IS NOT NULL THEN

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

Line 3824: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 3908: IF l_party_contains_str IS NULL THEN

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

Line 3911: IF l_party_site_contains_str IS NULL THEN

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

Line 3914: IF l_contact_contains_str IS NULL THEN

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

Line 3917: IF l_contact_pt_contains_str IS NULL THEN

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

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

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

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

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

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

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

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

Line 3932: IF l_contact_contains_str IS NOT NULL THEN

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

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

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

Line 4741: l_party_contains_str VARCHAR2(32000);

4737: x_num_matches OUT NUMBER
4738: ) IS
4739:
4740: -- Strings to hold the generated Intermedia query strings
4741: l_party_contains_str VARCHAR2(32000);
4742: l_match_idx NUMBER;
4743: l_party_site_contains_str VARCHAR2(32000);
4744: l_contact_contains_str VARCHAR2(32000);
4745: l_contact_pt_contains_str VARCHAR2(32000);

Line 4743: l_party_site_contains_str VARCHAR2(32000);

4739:
4740: -- Strings to hold the generated Intermedia query strings
4741: l_party_contains_str VARCHAR2(32000);
4742: l_match_idx NUMBER;
4743: l_party_site_contains_str VARCHAR2(32000);
4744: l_contact_contains_str VARCHAR2(32000);
4745: l_contact_pt_contains_str VARCHAR2(32000);
4746: l_tmp VARCHAR2(32000);
4747:

Line 4744: l_contact_contains_str VARCHAR2(32000);

4740: -- Strings to hold the generated Intermedia query strings
4741: l_party_contains_str VARCHAR2(32000);
4742: l_match_idx NUMBER;
4743: l_party_site_contains_str VARCHAR2(32000);
4744: l_contact_contains_str VARCHAR2(32000);
4745: l_contact_pt_contains_str VARCHAR2(32000);
4746: l_tmp VARCHAR2(32000);
4747:
4748: -- Other local variables

Line 4745: l_contact_pt_contains_str VARCHAR2(32000);

4741: l_party_contains_str VARCHAR2(32000);
4742: l_match_idx NUMBER;
4743: l_party_site_contains_str VARCHAR2(32000);
4744: l_contact_contains_str VARCHAR2(32000);
4745: l_contact_pt_contains_str VARCHAR2(32000);
4746: l_tmp VARCHAR2(32000);
4747:
4748: -- Other local variables
4749: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

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

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

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

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

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

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

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

Line 4836: IF l_party_site_contains_str IS NULL THEN

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

Line 4839: IF l_contact_pt_contains_str IS NULL THEN

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

Line 4843: IF l_party_site_contains_str IS NOT NULL THEN

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

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

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

Line 4867: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

4871: ) THEN
4872: FORALL I in 1..l_match_ps_list.COUNT
4873: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4874: values (l_search_ctx_id,l_match_ps_list(I));
4875: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'PARTY_SITES');
4876: ELSE
4877: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4878: END IF;
4879: LOOP

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

4873: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4874: values (l_search_ctx_id,l_match_ps_list(I));
4875: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'PARTY_SITES');
4876: ELSE
4877: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4878: END IF;
4879: LOOP
4880: FETCH l_contact_pt_cur INTO
4881: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4977: l_party_contains_str VARCHAR2(32000);

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

Line 4978: l_party_site_contains_str VARCHAR2(32000);

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

Line 4979: l_contact_contains_str VARCHAR2(32000);

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

Line 4980: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

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

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

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

Line 5065: IF l_contact_contains_str IS NULL THEN

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

Line 5068: IF l_contact_pt_contains_str IS NULL THEN

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

Line 5072: IF l_contact_contains_str IS NOT NULL THEN

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

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

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

Line 5094: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

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

Line 5202: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

Line 5272: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

5269: 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);
5270: END IF;
5271:
5272: IF l_contact_pt_contains_str IS NOT NULL THEN
5273: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5274: cnt := 1;
5275: LOOP
5276: FETCH l_contact_pt_cur INTO
5277: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 5344: l_party_contains_str VARCHAR2(32000);

5340: x_search_ctx_id IN OUT NUMBER
5341: ) IS
5342:
5343: -- Strings to hold the generated Intermedia query strings
5344: l_party_contains_str VARCHAR2(32000);
5345: l_party_site_contains_str VARCHAR2(32000);
5346: l_contact_contains_str VARCHAR2(32000);
5347: l_contact_pt_contains_str VARCHAR2(32000);
5348: l_tmp VARCHAR2(32000);

Line 5345: l_party_site_contains_str VARCHAR2(32000);

5341: ) IS
5342:
5343: -- Strings to hold the generated Intermedia query strings
5344: l_party_contains_str VARCHAR2(32000);
5345: l_party_site_contains_str VARCHAR2(32000);
5346: l_contact_contains_str VARCHAR2(32000);
5347: l_contact_pt_contains_str VARCHAR2(32000);
5348: l_tmp VARCHAR2(32000);
5349:

Line 5346: l_contact_contains_str VARCHAR2(32000);

5342:
5343: -- Strings to hold the generated Intermedia query strings
5344: l_party_contains_str VARCHAR2(32000);
5345: l_party_site_contains_str VARCHAR2(32000);
5346: l_contact_contains_str VARCHAR2(32000);
5347: l_contact_pt_contains_str VARCHAR2(32000);
5348: l_tmp VARCHAR2(32000);
5349:
5350: -- Other local variables

Line 5347: l_contact_pt_contains_str VARCHAR2(32000);

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

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

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

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

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

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

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

Line 5444: IF l_party_site_contains_str IS NOT NULL THEN

5440: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX4,l_TX8,l_TX19,l_TX34,l_TX36,l_TX46,1);
5441: END LOOP;
5442: CLOSE l_party_cur;
5443:
5444: IF l_party_site_contains_str IS NOT NULL THEN
5445: l_max_score := 0;
5446: l_max_id := 0;
5447: l_max_idx := 0;
5448: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5445: l_max_score := 0;
5446: l_max_id := 0;
5447: l_max_idx := 0;
5448: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5449: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5450: ELSE
5451: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5452: END IF;
5453: LOOP

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

5447: l_max_idx := 0;
5448: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5449: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5450: ELSE
5451: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5452: END IF;
5453: LOOP
5454: FETCH l_party_site_cur INTO
5455: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX4, l_TX10, l_TX14, l_TX22;

Line 5474: IF l_contact_contains_str IS NOT NULL THEN

5470: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX4,l_max_TX10,l_max_TX14,l_max_TX22,l_max_idx);
5471: END IF;
5472: END IF;
5473:
5474: IF l_contact_contains_str IS NOT NULL THEN
5475: l_max_score := 0;
5476: l_max_id := 0;
5477: l_max_idx := 0;
5478: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

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

Line 5496: IF l_contact_pt_contains_str IS NOT NULL THEN

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

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

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

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

5499: l_max_idx := 0;
5500: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5501: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5502: ELSE
5503: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5504: END IF;
5505: LOOP
5506: FETCH l_contact_pt_cur INTO
5507: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;