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_6 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 1090: l_party_contains_str VARCHAR2(32000);

1086: p_party_max_score NUMBER,
1087: p_denorm_max_score NUMBER,
1088: p_non_denorm_max_score NUMBER,
1089: p_threshold NUMBER) RETURN VARCHAR2 IS
1090: l_party_contains_str VARCHAR2(32000);
1091: l_party_filter VARCHAR2(1) := null;
1092: l_prim_temp VARCHAR2(4000);
1093: l_denorm_str VARCHAR2(4000);
1094: BEGIN

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

1130:
1131: IF l_denorm_str IS NOT NULL THEN
1132: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1133: l_denorm_str := NULL;
1134: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1135: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1136: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1137: l_denorm_str := NULL;
1138: END IF;

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

1132: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1133: l_denorm_str := NULL;
1134: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1135: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1136: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1137: l_denorm_str := NULL;
1138: END IF;
1139: ELSE
1140: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1136: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1137: l_denorm_str := NULL;
1138: END IF;
1139: ELSE
1140: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1141: END IF;
1142:
1143: -- Setup query string for TAX_REFERENCE
1144: l_prim_temp := null;

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

1147:
1148: IF l_denorm_str IS NOT NULL THEN
1149: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1150: l_denorm_str := NULL;
1151: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1152: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1153: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1154: l_denorm_str := NULL;
1155: END IF;

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

1149: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1150: l_denorm_str := NULL;
1151: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1152: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1153: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1154: l_denorm_str := NULL;
1155: END IF;
1156: ELSE
1157: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1153: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1154: l_denorm_str := NULL;
1155: END IF;
1156: ELSE
1157: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1158: END IF;
1159:
1160: -- Setup query string for DUNS_NUMBER_C
1161: l_prim_temp := null;

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

1170:
1171: IF l_denorm_str IS NOT NULL THEN
1172: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1173: l_denorm_str := NULL;
1174: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1175: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1176: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1177: l_denorm_str := NULL;
1178: END IF;

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

1172: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1173: l_denorm_str := NULL;
1174: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1175: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1176: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1177: l_denorm_str := NULL;
1178: END IF;
1179: ELSE
1180: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1176: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1177: l_denorm_str := NULL;
1178: END IF;
1179: ELSE
1180: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1181: END IF;
1182: IF lengthb(l_party_contains_str) > 4000 THEN
1183: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1184: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 1182: IF lengthb(l_party_contains_str) > 4000 THEN

1178: END IF;
1179: ELSE
1180: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1181: END IF;
1182: IF lengthb(l_party_contains_str) > 4000 THEN
1183: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1184: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1185: FND_MSG_PUB.ADD;
1186: RAISE FND_API.G_EXC_ERROR;

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

1184: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1185: FND_MSG_PUB.ADD;
1186: RAISE FND_API.G_EXC_ERROR;
1187: END IF;
1188: IF (p_match_str = ' AND ' OR p_party_max_score 1189: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1190: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1191: END IF;
1192: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

1186: RAISE FND_API.G_EXC_ERROR;
1187: END IF;
1188: IF (p_match_str = ' AND ' OR p_party_max_score 1189: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1190: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1191: END IF;
1192: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1193: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1194: END IF;

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

1189: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1190: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1191: END IF;
1192: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1193: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1194: END IF;
1195: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1196: 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);
1197: END IF;

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

1194: END IF;
1195: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1196: 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);
1197: END IF;
1198: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1199: ELSE
1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1202: 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 1201: 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);

1197: END IF;
1198: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1199: ELSE
1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1202: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1203: END IF;
1204: RETURN l_party_contains_str;
1205: END IF;

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

1198: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1199: ELSE
1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1202: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1203: END IF;
1204: RETURN l_party_contains_str;
1205: END IF;
1206: END;

Line 1204: RETURN l_party_contains_str;

1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1202: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1203: END IF;
1204: RETURN l_party_contains_str;
1205: END IF;
1206: END;
1207: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1208: l_contains_str VARCHAR2(32000);

Line 1208: l_contains_str VARCHAR2(32000);

1204: RETURN l_party_contains_str;
1205: END IF;
1206: END;
1207: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1208: l_contains_str VARCHAR2(32000);
1209: l_contains_str_temp VARCHAR2(32000);
1210: l_den_contains_str VARCHAR2(32000);
1211: l_den_contains_str_temp VARCHAR2(32000);
1212: l_filter_str VARCHAR2(4000) := null;

Line 1209: l_contains_str_temp VARCHAR2(32000);

1205: END IF;
1206: END;
1207: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1208: l_contains_str VARCHAR2(32000);
1209: l_contains_str_temp VARCHAR2(32000);
1210: l_den_contains_str VARCHAR2(32000);
1211: l_den_contains_str_temp VARCHAR2(32000);
1212: l_filter_str VARCHAR2(4000) := null;
1213: l_prim_temp VARCHAR2(4000) := null;

Line 1210: l_den_contains_str VARCHAR2(32000);

1206: END;
1207: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1208: l_contains_str VARCHAR2(32000);
1209: l_contains_str_temp VARCHAR2(32000);
1210: l_den_contains_str VARCHAR2(32000);
1211: l_den_contains_str_temp VARCHAR2(32000);
1212: l_filter_str VARCHAR2(4000) := null;
1213: l_prim_temp VARCHAR2(4000) := null;
1214: l_prim_temp_den VARCHAR2(4000) := null;

Line 1211: l_den_contains_str_temp VARCHAR2(32000);

1207: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1208: l_contains_str VARCHAR2(32000);
1209: l_contains_str_temp VARCHAR2(32000);
1210: l_den_contains_str VARCHAR2(32000);
1211: l_den_contains_str_temp VARCHAR2(32000);
1212: l_filter_str VARCHAR2(4000) := null;
1213: l_prim_temp VARCHAR2(4000) := null;
1214: l_prim_temp_den VARCHAR2(4000) := null;
1215: BEGIN

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

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

Line 1226: l_contains_str_temp := null;

1222: --
1223: -- For each primary transformation add to intermedia query if it
1224: -- is not null
1225: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1226: l_contains_str_temp := null;
1227: l_den_contains_str_temp := null;
1228: l_filter_str := null;
1229:
1230: -- Setup query string for ADDRESS

Line 1227: l_den_contains_str_temp := null;

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

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

1231: l_prim_temp := null;
1232: l_prim_temp_den := null;
1233: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1234: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1235: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1236:
1237: -- Setup query string for POSTAL_CODE
1238: l_prim_temp := null;
1239: l_prim_temp_den := null;

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

1240: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1241: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1242: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1243: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1244: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1245: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1246:
1247: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1248: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

1241: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1242: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1243: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1244: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1245: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1246:
1247: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1248: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1249: END LOOP;

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

1243: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1244: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1245: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1246:
1247: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1248: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1249: END LOOP;
1250: -- Add the search criteria to query string
1251: IF lengthb(l_contains_str) > 4000 THEN

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

1244: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1245: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1246:
1247: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1248: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1249: END LOOP;
1250: -- Add the search criteria to query string
1251: IF lengthb(l_contains_str) > 4000 THEN
1252: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1251: IF lengthb(l_contains_str) > 4000 THEN

1247: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1248: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1249: END LOOP;
1250: -- Add the search criteria to query string
1251: IF lengthb(l_contains_str) > 4000 THEN
1252: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1253: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1254: FND_MSG_PUB.ADD;
1255: RAISE FND_API.G_EXC_ERROR;

Line 1257: x_denorm_str := l_den_contains_str;

1253: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1254: FND_MSG_PUB.ADD;
1255: RAISE FND_API.G_EXC_ERROR;
1256: END IF;
1257: x_denorm_str := l_den_contains_str;
1258: RETURN l_contains_str;
1259: END;
1260:
1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1258: RETURN l_contains_str;

1254: FND_MSG_PUB.ADD;
1255: RAISE FND_API.G_EXC_ERROR;
1256: END IF;
1257: x_denorm_str := l_den_contains_str;
1258: RETURN l_contains_str;
1259: END;
1260:
1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1262: l_contains_str VARCHAR2(32000);

Line 1262: l_contains_str VARCHAR2(32000);

1258: RETURN l_contains_str;
1259: END;
1260:
1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1262: l_contains_str VARCHAR2(32000);
1263: l_contains_str_temp VARCHAR2(32000);
1264: l_den_contains_str VARCHAR2(32000);
1265: l_den_contains_str_temp VARCHAR2(32000);
1266: l_filter_str VARCHAR2(4000) := null;

Line 1263: l_contains_str_temp VARCHAR2(32000);

1259: END;
1260:
1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1262: l_contains_str VARCHAR2(32000);
1263: l_contains_str_temp VARCHAR2(32000);
1264: l_den_contains_str VARCHAR2(32000);
1265: l_den_contains_str_temp VARCHAR2(32000);
1266: l_filter_str VARCHAR2(4000) := null;
1267: l_prim_temp VARCHAR2(4000) := null;

Line 1264: l_den_contains_str VARCHAR2(32000);

1260:
1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1262: l_contains_str VARCHAR2(32000);
1263: l_contains_str_temp VARCHAR2(32000);
1264: l_den_contains_str VARCHAR2(32000);
1265: l_den_contains_str_temp VARCHAR2(32000);
1266: l_filter_str VARCHAR2(4000) := null;
1267: l_prim_temp VARCHAR2(4000) := null;
1268: l_prim_temp_den VARCHAR2(4000) := null;

Line 1265: l_den_contains_str_temp VARCHAR2(32000);

1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1262: l_contains_str VARCHAR2(32000);
1263: l_contains_str_temp VARCHAR2(32000);
1264: l_den_contains_str VARCHAR2(32000);
1265: l_den_contains_str_temp VARCHAR2(32000);
1266: l_filter_str VARCHAR2(4000) := null;
1267: l_prim_temp VARCHAR2(4000) := null;
1268: l_prim_temp_den VARCHAR2(4000) := null;
1269: BEGIN

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

1271: hz_utility_v2pub.debug(p_message=>'INIT_CONTACTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1272: END IF;
1273: x_denorm_str := NULL;
1274:
1275: -- Dynamic setup of party site contains str
1276: --
1277: -- For each primary transformation add to intermedia query if it
1278: -- is not null
1279: FOR I IN 1..g_contact_stage_list.COUNT LOOP

Line 1280: l_contains_str_temp := null;

1276: --
1277: -- For each primary transformation add to intermedia query if it
1278: -- is not null
1279: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1280: l_contains_str_temp := null;
1281: l_den_contains_str_temp := null;
1282: l_filter_str := null;
1283:
1284: -- Setup query string for CONTACT_NAME

Line 1281: l_den_contains_str_temp := null;

1277: -- For each primary transformation add to intermedia query if it
1278: -- is not null
1279: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1280: l_contains_str_temp := null;
1281: l_den_contains_str_temp := null;
1282: l_filter_str := null;
1283:
1284: -- Setup query string for CONTACT_NAME
1285: l_prim_temp := null;

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

1285: l_prim_temp := null;
1286: l_prim_temp_den := null;
1287: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRPERSON
1288: g_contact_stage_list(I).TX6,'A39',l_prim_temp);
1289: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1290:
1291: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1292: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1293: END LOOP;

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

1287: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRPERSON
1288: g_contact_stage_list(I).TX6,'A39',l_prim_temp);
1289: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1290:
1291: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1292: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1293: END LOOP;
1294: -- Add the search criteria to query string
1295: IF lengthb(l_contains_str) > 4000 THEN

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

1288: g_contact_stage_list(I).TX6,'A39',l_prim_temp);
1289: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1290:
1291: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1292: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1293: END LOOP;
1294: -- Add the search criteria to query string
1295: IF lengthb(l_contains_str) > 4000 THEN
1296: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1295: IF lengthb(l_contains_str) > 4000 THEN

1291: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1292: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1293: END LOOP;
1294: -- Add the search criteria to query string
1295: IF lengthb(l_contains_str) > 4000 THEN
1296: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1297: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1298: FND_MSG_PUB.ADD;
1299: RAISE FND_API.G_EXC_ERROR;

Line 1301: x_denorm_str := l_den_contains_str;

1297: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1298: FND_MSG_PUB.ADD;
1299: RAISE FND_API.G_EXC_ERROR;
1300: END IF;
1301: x_denorm_str := l_den_contains_str;
1302: RETURN l_contains_str;
1303: END;
1304:
1305: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1302: RETURN l_contains_str;

1298: FND_MSG_PUB.ADD;
1299: RAISE FND_API.G_EXC_ERROR;
1300: END IF;
1301: x_denorm_str := l_den_contains_str;
1302: RETURN l_contains_str;
1303: END;
1304:
1305: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1306: l_contains_str VARCHAR2(32000);

Line 1306: l_contains_str VARCHAR2(32000);

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

Line 1307: l_contains_str_temp VARCHAR2(32000);

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

Line 1308: l_den_contains_str VARCHAR2(32000);

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

Line 1309: l_den_contains_str_temp VARCHAR2(32000);

1305: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1306: l_contains_str VARCHAR2(32000);
1307: l_contains_str_temp VARCHAR2(32000);
1308: l_den_contains_str VARCHAR2(32000);
1309: l_den_contains_str_temp VARCHAR2(32000);
1310: l_filter_str VARCHAR2(4000) := null;
1311: l_prim_temp VARCHAR2(4000) := null;
1312: l_prim_temp_den VARCHAR2(4000) := null;
1313: BEGIN

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

1315: hz_utility_v2pub.debug(p_message=>'INIT_CONTACT_POINTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1316: END IF;
1317: x_denorm_str := NULL;
1318:
1319: -- Dynamic setup of party site contains str
1320: --
1321: -- For each primary transformation add to intermedia query if it
1322: -- is not null
1323: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP

Line 1324: l_contains_str_temp := null;

1320: --
1321: -- For each primary transformation add to intermedia query if it
1322: -- is not null
1323: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1324: l_contains_str_temp := null;
1325: l_den_contains_str_temp := null;
1326: l_filter_str := null;
1327: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1328:

Line 1325: l_den_contains_str_temp := null;

1321: -- For each primary transformation add to intermedia query if it
1322: -- is not null
1323: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1324: l_contains_str_temp := null;
1325: l_den_contains_str_temp := null;
1326: l_filter_str := null;
1327: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1328:
1329: -- Setup query string for FLEX_FORMAT_PHONE_NUMBER

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

1330: l_prim_temp := null;
1331: l_prim_temp_den := null;
1332: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
1333: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1334: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1335:
1336: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1337: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1338: END LOOP;

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

1332: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
1333: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1334: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1335:
1336: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1337: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1338: END LOOP;
1339: -- Add the search criteria to query string
1340: IF lengthb(l_contains_str) > 4000 THEN

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

1333: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1334: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1335:
1336: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1337: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1338: END LOOP;
1339: -- Add the search criteria to query string
1340: IF lengthb(l_contains_str) > 4000 THEN
1341: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1340: IF lengthb(l_contains_str) > 4000 THEN

1336: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1337: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1338: END LOOP;
1339: -- Add the search criteria to query string
1340: IF lengthb(l_contains_str) > 4000 THEN
1341: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1342: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1343: FND_MSG_PUB.ADD;
1344: RAISE FND_API.G_EXC_ERROR;

Line 1346: x_denorm_str := l_den_contains_str;

1342: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1343: FND_MSG_PUB.ADD;
1344: RAISE FND_API.G_EXC_ERROR;
1345: END IF;
1346: x_denorm_str := l_den_contains_str;
1347: RETURN l_contains_str;
1348: END;
1349:
1350: PROCEDURE open_party_cursor_direct (

Line 1347: RETURN l_contains_str;

1343: FND_MSG_PUB.ADD;
1344: RAISE FND_API.G_EXC_ERROR;
1345: END IF;
1346: x_denorm_str := l_den_contains_str;
1347: RETURN l_contains_str;
1348: END;
1349:
1350: PROCEDURE open_party_cursor_direct (
1351: p_dup_party_id NUMBER,

Line 1355: p_party_contains_str VARCHAR2,

1351: p_dup_party_id NUMBER,
1352: p_restrict_sql VARCHAR2,
1353: p_match_str VARCHAR2,
1354: p_search_merged VARCHAR2,
1355: p_party_contains_str VARCHAR2,
1356: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1357: l_sqlstr VARCHAR2(4000);
1358: l_search_merged VARCHAR2(1);
1359: BEGIN

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

1368: END IF;
1369: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1370: 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);
1371: END IF;
1372: 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
1373: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1374: 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);
1375: END IF;
1376: OPEN x_cursor FOR

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

1403: l_sqlstr := l_sqlstr || ' (TX44 like :TX44||'' %'' ';
1404: l_sqlstr := l_sqlstr || ' )) ';
1405: l_sqlstr := l_sqlstr || ' ) ';
1406: END IF;
1407: 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
1408: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1409: 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);
1410: END IF;
1411: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

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

1405: l_sqlstr := l_sqlstr || ' ) ';
1406: END IF;
1407: 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
1408: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1409: 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);
1410: END IF;
1411: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1412: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX11||'' %'' ';
1413: l_sqlstr := l_sqlstr || ' )) ';

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

1426: END IF;
1427: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1428: 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);
1429: END IF;
1430: 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
1431: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1432: 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);
1433: END IF;
1434: 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 1432: 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);

1428: 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);
1429: END IF;
1430: 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
1431: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1432: 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);
1433: END IF;
1434: 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
1435: ,g_party_stage_rec.TX41
1436: ,g_party_stage_rec.TX41

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

1448: ,g_party_stage_rec.TX41
1449: ,g_party_stage_rec.TX44
1450: ,g_party_stage_rec.TX44
1451: ;
1452: 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
1453: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1454: 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);
1455: END IF;
1456: 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 1454: 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);

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

Line 1471: p_contains_str VARCHAR2,

1467: END;
1468: PROCEDURE open_party_cursor(
1469: p_dup_party_id NUMBER,
1470: p_restrict_sql VARCHAR2,
1471: p_contains_str VARCHAR2,
1472: p_search_ctx_id NUMBER,
1473: p_match_str VARCHAR2,
1474: p_search_merged VARCHAR2,
1475: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1490: IF p_contains_str IS NULL THEN

1486: l_search_merged := 'N';
1487: ELSE
1488: l_search_merged := p_search_merged;
1489: END IF;
1490: IF p_contains_str IS NULL THEN
1491: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1492: hz_utility_v2pub.debug(p_message=>'part contains string is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1493: END IF;
1494: OPEN x_cursor FOR

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

1488: l_search_merged := p_search_merged;
1489: END IF;
1490: IF p_contains_str IS NULL THEN
1491: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1492: hz_utility_v2pub.debug(p_message=>'part contains string is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1493: END IF;
1494: OPEN x_cursor FOR
1495: SELECT PARTY_ID , TX2, TX8, TX4, lpad(rtrim(TX41),9,chr(48)), TX44, TX46
1496: FROM HZ_STAGED_PARTIES stage

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

1505: END IF;
1506: OPEN x_cursor FOR
1507: SELECT PARTY_ID , TX2, TX8, TX4, lpad(rtrim(TX41),9,chr(48)), TX44, TX46
1508: FROM HZ_STAGED_PARTIES stage
1509: WHERE contains( concat_col, p_contains_str)>0
1510: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1511: AND( (l_search_merged ='Y' )
1512: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1513: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1518: END IF;
1519: OPEN x_cursor FOR
1520: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX2, TX8, TX4, lpad(rtrim(TX41),9,chr(48)), TX44, TX46
1521: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1522: WHERE contains( concat_col, p_contains_str)>0
1523: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1524: AND d.party_id = stage.party_id
1525: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1526: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1541: END IF;
1542: IF p_search_ctx_id IS NULL THEN
1543: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX2, TX8, TX4, lpad(rtrim(TX41),9,chr(48)), TX44, TX46'||
1544: ' FROM HZ_STAGED_PARTIES stage'||
1545: ' WHERE contains( concat_col, :cont)>0'||
1546: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1547: ' AND ('||p_restrict_sql||')' ||
1548: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1549: IF l_search_merged = 'Y' THEN

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

1553: ELSE
1554: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1555: END IF;
1556: output_long_strings('----------------------------------------------------------');
1557: output_long_strings('Party Contains String = '||p_contains_str);
1558: output_long_strings('Restrict Sql = '||p_restrict_sql);
1559: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1560: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1561: ,p_dup_party_id, p_dup_party_id;

Line 1559: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1555: END IF;
1556: output_long_strings('----------------------------------------------------------');
1557: output_long_strings('Party Contains String = '||p_contains_str);
1558: output_long_strings('Restrict Sql = '||p_restrict_sql);
1559: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1560: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1561: ,p_dup_party_id, p_dup_party_id;
1562: END IF;
1563: END IF;

Line 1590: p_contains_str VARCHAR2,

1586: PROCEDURE open_party_site_cursor(
1587: p_dup_party_id NUMBER,
1588: p_party_id NUMBER,
1589: p_restrict_sql VARCHAR2,
1590: p_contains_str VARCHAR2,
1591: p_search_ctx_id NUMBER,
1592: p_search_merged VARCHAR2,
1593: p_search_rel_sites VARCHAR2,
1594: p_person_api VARCHAR2,

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

1618: IF p_search_rel_sites = 'N' THEN
1619: OPEN x_cursor FOR
1620: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1621: FROM HZ_STAGED_PARTY_SITES stage
1622: WHERE contains( concat_col, p_contains_str)>0
1623: AND( (l_search_merged ='Y' )
1624: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1625: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1626: AND stage.party_id = p_party_id;

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

1627: ELSE
1628: OPEN x_cursor FOR
1629: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1630: FROM HZ_STAGED_PARTY_SITES stage
1631: WHERE contains( concat_col, p_contains_str)>0
1632: AND( (l_search_merged ='Y' )
1633: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1634: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1635: AND stage.party_id = p_party_id

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

1635: AND stage.party_id = p_party_id
1636: UNION
1637: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX11
1638: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1639: WHERE contains( concat_col, p_contains_str)>0
1640: AND( (l_search_merged ='Y' )
1641: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1642: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1643: AND r.object_id = p_party_id

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

1657: END IF;
1658: OPEN x_cursor FOR
1659: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1660: FROM HZ_STAGED_PARTY_SITES stage
1661: WHERE contains( concat_col, p_contains_str)>0
1662: AND EXISTS (
1663: SELECT 1 FROM HZ_STAGED_PARTIES p
1664: WHERE p.PARTY_ID = stage.PARTY_ID
1665: AND( (l_search_merged ='Y' )

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

1677: IF p_person_api = 'Y' THEN
1678: OPEN x_cursor FOR
1679: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1680: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1681: WHERE contains( concat_col, p_contains_str)>0
1682: AND( (l_search_merged ='Y' )
1683: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1684: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1685: AND d.search_context_id = p_search_ctx_id

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

1687: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1688: UNION
1689: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX11
1690: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1691: WHERE contains( concat_col, p_contains_str)>0
1692: AND( (l_search_merged ='Y' )
1693: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1694: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1695: AND d.search_context_id = p_search_ctx_id

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

1700: ELSE
1701: OPEN x_cursor FOR
1702: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1703: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1704: WHERE contains( concat_col, p_contains_str)>0
1705: AND( (l_search_merged ='Y' )
1706: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1707: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1708: AND d.search_context_id = p_search_ctx_id

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

1745: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1746: END IF;
1747: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1748: ' FROM HZ_STAGED_PARTY_SITES stage'||
1749: ' WHERE contains( concat_col, :cont)>0'||
1750: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1751: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1752: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1753: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1755: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1756: ' UNION ' ||
1757: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1758: ' FROM HZ_STAGED_PARTY_SITES stage'||
1759: ' WHERE contains( concat_col, :cont)>0'||
1760: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1761: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1762: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1763: ' AND ORG_CONTACT_ID IN ' ||

Line 1769: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1765: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1766: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1767: ' and ('||p_restrict_sql1|| ') )' ||
1768: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1769: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1770: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1771: ELSE
1772: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1773: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1770: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1766: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1767: ' and ('||p_restrict_sql1|| ') )' ||
1768: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1769: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1770: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1771: ELSE
1772: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1773: ' FROM HZ_STAGED_PARTY_SITES stage'||
1774: ' WHERE contains( concat_col, :cont)>0'||

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

1770: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1771: ELSE
1772: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1773: ' FROM HZ_STAGED_PARTY_SITES stage'||
1774: ' WHERE contains( concat_col, :cont)>0'||
1775: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1776: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1777: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1778: ' AND EXISTS ('||

Line 1784: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1780: ' WHERE p.party_id = stage.party_id ' ||
1781: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1782: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1783: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1784: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1785: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1786: ,p_dup_party_id, p_dup_party_id;
1787: END IF;
1788: ELSE

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

1787: END IF;
1788: ELSE
1789: l_sqlstr := 'SELECT ' || l_hint ||' PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1790: ' FROM HZ_STAGED_PARTY_SITES stage'||
1791: ' WHERE contains( concat_col, :cont)>0'||
1792: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1793: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1794: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1795: ' AND EXISTS ('||

Line 1801: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1797: ' WHERE p.party_id = stage.party_id ' ||
1798: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1799: ' AND ('||p_restrict_sql||')' ||
1800: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1801: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1802: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1803: ,p_dup_party_id, p_dup_party_id;
1804: END IF;
1805: END IF;

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

1803: ,p_dup_party_id, p_dup_party_id;
1804: END IF;
1805: END IF;
1806: output_long_strings('----------------------------------------------------------');
1807: output_long_strings('Party Site Contains String = '||p_contains_str);
1808: output_long_strings('Restrict Sql = '||p_restrict_sql);
1809: exception
1810: when others then
1811: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1835: p_contains_str VARCHAR2,

1831: PROCEDURE open_contact_cursor(
1832: p_dup_party_id NUMBER,
1833: p_party_id NUMBER,
1834: p_restrict_sql VARCHAR2,
1835: p_contains_str VARCHAR2,
1836: p_search_ctx_id NUMBER,
1837: p_search_merged VARCHAR2,
1838: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1839: l_sqlstr VARCHAR2(4000);

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

1859: END IF;
1860: OPEN x_cursor FOR
1861: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX2, TX6, TX5
1862: FROM HZ_STAGED_CONTACTS stage
1863: WHERE contains( concat_col, p_contains_str)>0
1864: AND EXISTS (
1865: SELECT 1 FROM HZ_STAGED_PARTIES p
1866: WHERE p.PARTY_ID = stage.PARTY_ID
1867: AND( (l_search_merged ='Y' )

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

1882: END IF;
1883: OPEN x_cursor FOR
1884: SELECT ORG_CONTACT_ID, PARTY_ID, TX2, TX6, TX5
1885: FROM HZ_STAGED_CONTACTS stage
1886: WHERE contains( concat_col, p_contains_str)>0
1887: AND EXISTS (
1888: SELECT 1 FROM HZ_STAGED_PARTIES p
1889: WHERE p.PARTY_ID = stage.PARTY_ID
1890: AND( (l_search_merged ='Y' )

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

1901: END IF;
1902: OPEN x_cursor FOR
1903: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX2, TX6, TX5
1904: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1905: WHERE contains( concat_col, p_contains_str)>0
1906: AND d.search_context_id = p_search_ctx_id
1907: AND d.party_id = stage.party_id
1908: AND( (l_search_merged ='Y' )
1909: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1935: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1936: END IF;
1937: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX2, TX6, TX5'||
1938: ' FROM HZ_STAGED_CONTACTS stage'||
1939: ' WHERE contains( concat_col, :cont)>0'||
1940: ' AND EXISTS ('||
1941: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1942: ' WHERE p.party_id = stage.party_id ' ||
1943: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1949: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

1950: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1951: ,p_dup_party_id, p_dup_party_id;
1952: END IF;
1953: output_long_strings('----------------------------------------------------------');
1954: output_long_strings('Contacts Contains String = '||p_contains_str);
1955: output_long_strings('Restrict Sql = '||p_restrict_sql);
1956: exception
1957: when others then
1958: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1982: p_contains_str VARCHAR2,

1978: PROCEDURE open_contact_pt_cursor(
1979: p_dup_party_id NUMBER,
1980: p_party_id NUMBER,
1981: p_restrict_sql VARCHAR2,
1982: p_contains_str VARCHAR2,
1983: p_search_ctx_id NUMBER,
1984: p_search_merged VARCHAR2,
1985: p_search_rel_cpts VARCHAR2,
1986: p_person_api VARCHAR2,

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

2008: THEN
2009: OPEN x_cursor FOR
2010: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2011: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2012: WHERE contains( concat_col, p_contains_str)>0
2013: AND d.search_context_id = p_search_ctx_id
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') )

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

2020: THEN
2021: OPEN x_cursor FOR
2022: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2023: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2024: WHERE contains( concat_col, p_contains_str)>0
2025: AND d.search_context_id = p_search_ctx_id
2026: AND( (l_search_merged ='Y' )
2027: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2028: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

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

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

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

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

2054: AND stage.party_id = p_party_id
2055: UNION
2056: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1
2057: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2058: WHERE contains( concat_col, p_contains_str)>0
2059: AND r.object_id = p_party_id
2060: AND( (l_search_merged ='Y' )
2061: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2062: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2076: END IF;
2077: OPEN x_cursor FOR
2078: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2079: FROM HZ_STAGED_CONTACT_POINTS stage
2080: WHERE contains( concat_col, p_contains_str)>0
2081: AND EXISTS (
2082: SELECT 1 FROM HZ_STAGED_PARTIES p
2083: WHERE p.PARTY_ID = stage.PARTY_ID
2084: AND( (l_search_merged ='Y' )

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

2096: IF p_person_api = 'Y' THEN
2097: OPEN x_cursor FOR
2098: 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 , TX1
2099: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2100: WHERE contains( concat_col, p_contains_str)>0
2101: AND d.search_context_id = p_search_ctx_id
2102: AND d.party_id = stage.party_id
2103: AND( (l_search_merged ='Y' )
2104: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2106: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2107: UNION
2108: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1
2109: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2110: WHERE contains( concat_col, p_contains_str)>0
2111: AND d.search_context_id = p_search_ctx_id
2112: AND d.party_id = r.subject_id
2113: AND r.relationship_id = oc.party_relationship_id
2114: AND oc.org_contact_id = stage.org_contact_id

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

2119: ELSE
2120: OPEN x_cursor FOR
2121: 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 , TX1
2122: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2123: WHERE contains( concat_col, p_contains_str)>0
2124: AND d.search_context_id = p_search_ctx_id
2125: AND d.party_id = stage.party_id
2126: AND( (l_search_merged ='Y' )
2127: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2164: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2165: END IF;
2166: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2167: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2168: ' WHERE contains( concat_col, :cont)>0 '||
2169: ' AND (stage.org_contact_id is null '||
2170: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
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'') ) '||

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

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

Line 2188: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

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

Line 2189: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

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

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

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

Line 2203: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

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

Line 2220: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

2223: END IF;
2224: END IF;
2225: END IF;
2226: output_long_strings('----------------------------------------------------------');
2227: output_long_strings('Contact Points Contains String = '||p_contains_str);
2228: output_long_strings('Restrict Sql = '||p_restrict_sql);
2229: exception
2230: when others then
2231: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2348: l_party_contains_str VARCHAR2(32000);

2344: x_num_matches OUT NUMBER
2345: ) IS
2346:
2347: -- Strings to hold the generated Intermedia query strings
2348: l_party_contains_str VARCHAR2(32000);
2349: l_party_site_contains_str VARCHAR2(32000);
2350: l_contact_contains_str VARCHAR2(32000);
2351: l_contact_pt_contains_str VARCHAR2(32000);
2352: l_denorm_str VARCHAR2(32000);

Line 2349: l_party_site_contains_str VARCHAR2(32000);

2345: ) IS
2346:
2347: -- Strings to hold the generated Intermedia query strings
2348: l_party_contains_str VARCHAR2(32000);
2349: l_party_site_contains_str VARCHAR2(32000);
2350: l_contact_contains_str VARCHAR2(32000);
2351: l_contact_pt_contains_str VARCHAR2(32000);
2352: l_denorm_str VARCHAR2(32000);
2353: l_ps_denorm_str VARCHAR2(32000);

Line 2350: l_contact_contains_str VARCHAR2(32000);

2346:
2347: -- Strings to hold the generated Intermedia query strings
2348: l_party_contains_str VARCHAR2(32000);
2349: l_party_site_contains_str VARCHAR2(32000);
2350: l_contact_contains_str VARCHAR2(32000);
2351: l_contact_pt_contains_str VARCHAR2(32000);
2352: l_denorm_str VARCHAR2(32000);
2353: l_ps_denorm_str VARCHAR2(32000);
2354: l_ct_denorm_str VARCHAR2(32000);

Line 2351: l_contact_pt_contains_str VARCHAR2(32000);

2347: -- Strings to hold the generated Intermedia query strings
2348: l_party_contains_str VARCHAR2(32000);
2349: l_party_site_contains_str VARCHAR2(32000);
2350: l_contact_contains_str VARCHAR2(32000);
2351: l_contact_pt_contains_str VARCHAR2(32000);
2352: l_denorm_str VARCHAR2(32000);
2353: l_ps_denorm_str VARCHAR2(32000);
2354: l_ct_denorm_str VARCHAR2(32000);
2355: l_cpt_denorm_str VARCHAR2(32000);

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

2443: call_type(2) := 'OR';
2444: END;
2445:
2446: /** Private procedure to acquire and score at party level ***/
2447: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2448: l_party_id_idx NUMBER:=1;
2449: l_ctx_id NUMBER;
2450: l_precalc_score BOOLEAN := FALSE;
2451: BEGIN

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

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

2482: 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
2483: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2484: 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);
2485: END IF;
2486: IF (l_party_site_contains_str IS NULL AND
2487: l_contact_contains_str IS NULL AND
2488: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2489: 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);
2490: RAISE L_RETURN_IMM_EXC;

Line 2487: l_contact_contains_str IS NULL AND

2483: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2484: 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);
2485: END IF;
2486: IF (l_party_site_contains_str IS NULL AND
2487: l_contact_contains_str IS NULL AND
2488: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2489: 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);
2490: RAISE L_RETURN_IMM_EXC;
2491: ELSE

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

2484: 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);
2485: END IF;
2486: IF (l_party_site_contains_str IS NULL AND
2487: l_contact_contains_str IS NULL AND
2488: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2489: 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);
2490: RAISE L_RETURN_IMM_EXC;
2491: ELSE
2492: 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 2492: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2488: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2489: 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);
2490: RAISE L_RETURN_IMM_EXC;
2491: ELSE
2492: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2493: END IF;
2494: ELSE
2495: IF p_index>1 THEN
2496: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2511: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2512: 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);
2513: END IF;
2514: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2515: 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);
2516: END IF;
2517: LOOP
2518: FETCH l_party_cur INTO
2519: l_party_id , l_TX2, l_TX8, l_TX4, l_TX41, l_TX44, l_TX46;

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

2533: l_party_id_idx:= l_party_id_idx+1;
2534: END IF;
2535: IF (l_party_id_idx-1)>l_max_thresh THEN
2536: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2537: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2538: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2539: AND l_contact_pt_contains_str IS NULL THEN
2540: EXIT;
2541: END IF;

Line 2539: AND l_contact_pt_contains_str IS NULL THEN

2535: IF (l_party_id_idx-1)>l_max_thresh THEN
2536: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2537: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2538: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2539: AND l_contact_pt_contains_str IS NULL THEN
2540: EXIT;
2541: END IF;
2542: CLOSE l_party_cur;
2543: IF p_index>1 THEN

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

2564: ROLLBACK to eval_start;
2565: END;
2566:
2567: /** Private procedure to acquire and score at party site level ***/
2568: 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
2569: l_party_id_idx NUMBER:=1;
2570: l_ctx_id NUMBER;
2571: h_ps_id HZ_PARTY_SEARCH.IDList;
2572: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2576: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2577: 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);
2578: 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);
2579: END IF;
2580: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2581: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2582: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2583: l_party_id := H_SCORES.FIRST;
2584: WHILE l_party_id IS NOT NULL LOOP

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

2577: 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);
2578: 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);
2579: END IF;
2580: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2581: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2582: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2583: l_party_id := H_SCORES.FIRST;
2584: WHILE l_party_id IS NOT NULL LOOP
2585: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

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

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

2608: l_search_ctx_id,H_PARTY_ID(I));
2609: l_party_id_idx := H_PARTY_ID.COUNT+1;
2610: ELSE
2611: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2612: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2613: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2614: 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);
2615: END IF;
2616: IF p_index>1 THEN

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

2630: END IF;
2631: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2632: 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);
2633: END IF;
2634: 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);
2635: LOOP
2636: FETCH l_party_site_cur INTO
2637: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
2638: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2686: END IF;
2687: END;
2688:
2689: /** Private procedure to acquire and score at contact point level ***/
2690: 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
2691: l_party_id_idx NUMBER:=1;
2692: l_ctx_id NUMBER;
2693: h_ct_id HZ_PARTY_SEARCH.IDList;
2694: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2720: l_search_ctx_id,H_PARTY_ID(I));
2721: l_party_id_idx := H_PARTY_ID.COUNT+1;
2722: ELSE
2723: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2724: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2725: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2726: 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);
2727: END IF;
2728: IF p_index>1 THEN

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

2742: END IF;
2743: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2744: 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);
2745: END IF;
2746: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2747: LOOP
2748: FETCH l_contact_cur INTO
2749: l_org_contact_id, l_ct_party_id , l_TX2, l_TX6, l_TX5;
2750: EXIT WHEN l_contact_cur%NOTFOUND;

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

2795: END IF;
2796: END;
2797:
2798: /** Private procedure to acquire and score at contact point level ***/
2799: 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
2800: l_party_id_idx NUMBER:=1;
2801: l_ctx_id NUMBER;
2802: h_cpt_id HZ_PARTY_SEARCH.IDList;
2803: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2831: l_search_ctx_id,H_PARTY_ID(I));
2832: l_party_id_idx := H_PARTY_ID.COUNT+1;
2833: ELSE
2834: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2835: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2836: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2837: 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);
2838: END IF;
2839: IF p_index>1 THEN

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

2853: END IF;
2854: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2855: 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);
2856: END IF;
2857: 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);
2858: LOOP
2859: FETCH l_contact_pt_cur INTO
2860: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;
2861: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2957: IF l_party_contains_str IS NOT NULL THEN

2953: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2954: l_threshold := 100;
2955: other_acq_criteria_exists := TRUE ;
2956: --check if acquisition criteria exists for any other entity
2957: IF l_party_contains_str IS NOT NULL THEN
2958: acq_cnt := 1;
2959: END IF;
2960: IF l_party_site_contains_str IS NOT NULL THEN
2961: acq_cnt := acq_cnt+1;

Line 2960: IF l_party_site_contains_str IS NOT NULL THEN

2956: --check if acquisition criteria exists for any other entity
2957: IF l_party_contains_str IS NOT NULL THEN
2958: acq_cnt := 1;
2959: END IF;
2960: IF l_party_site_contains_str IS NOT NULL THEN
2961: acq_cnt := acq_cnt+1;
2962: END IF;
2963: IF l_contact_contains_str IS NOT NULL THEN
2964: acq_cnt := acq_cnt+1;

Line 2963: IF l_contact_contains_str IS NOT NULL THEN

2959: END IF;
2960: IF l_party_site_contains_str IS NOT NULL THEN
2961: acq_cnt := acq_cnt+1;
2962: END IF;
2963: IF l_contact_contains_str IS NOT NULL THEN
2964: acq_cnt := acq_cnt+1;
2965: END IF;
2966: IF l_contact_pt_contains_str IS NOT NULL THEN
2967: acq_cnt := acq_cnt+1;

Line 2966: IF l_contact_pt_contains_str IS NOT NULL THEN

2962: END IF;
2963: IF l_contact_contains_str IS NOT NULL THEN
2964: acq_cnt := acq_cnt+1;
2965: END IF;
2966: IF l_contact_pt_contains_str IS NOT NULL THEN
2967: acq_cnt := acq_cnt+1;
2968: END IF;
2969: IF acq_cnt>1 THEN
2970: other_acq_criteria_exists := TRUE;

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

2996: FND_MSG_PUB.ADD;
2997: RAISE FND_API.G_EXC_ERROR;
2998: END IF;
2999: END IF;
3000: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3001: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3002: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3003: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3004: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

2997: RAISE FND_API.G_EXC_ERROR;
2998: END IF;
2999: END IF;
3000: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3001: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3002: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3003: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3004: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3005: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2998: END IF;
2999: END IF;
3000: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3001: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3002: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3003: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3004: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3005: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3006: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

2999: END IF;
3000: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3001: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3002: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3003: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3004: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3005: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3006: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3007: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3000: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3001: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3002: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3003: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3004: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3005: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3006: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3007: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3008: END IF;

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

3001: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3002: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3003: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3004: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3005: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3006: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3007: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3008: END IF;
3009: END;

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

3002: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3003: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3004: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3005: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3006: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3007: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3008: END IF;
3009: END;
3010: /************ End of find_parties private procedures **********/

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

3003: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3004: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3005: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3006: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3007: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3008: END IF;
3009: END;
3010: /************ End of find_parties private procedures **********/
3011:

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

3086: 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);
3087: END IF;
3088: -- ************************************************************
3089: -- STEP 2. Setup of intermedia query strings for Acquisition query
3090: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3091: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3092: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3093: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3094: 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 3091: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

3087: END IF;
3088: -- ************************************************************
3089: -- STEP 2. Setup of intermedia query strings for Acquisition query
3090: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3091: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3092: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3093: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3094: 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);
3095: END IF;

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

3088: -- ************************************************************
3089: -- STEP 2. Setup of intermedia query strings for Acquisition query
3090: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3091: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3092: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3093: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3094: 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);
3095: END IF;
3096: l_denorm_max_score:=0;

Line 3124: 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/445)*l_entered_max_score));

3120: END IF;
3121: ELSE
3122: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3123: END IF;
3124: 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/445)*l_entered_max_score));
3125: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3126:
3127: -- Setup Search Context ID
3128: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3134: IF l_party_contains_str IS NULL THEN

3130:
3131: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3132: 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);
3133: END IF;
3134: IF l_party_contains_str IS NULL THEN
3135: defpt := 1;
3136: END IF;
3137: IF l_party_site_contains_str IS NULL THEN
3138: defps := 1;

Line 3137: IF l_party_site_contains_str IS NULL THEN

3133: END IF;
3134: IF l_party_contains_str IS NULL THEN
3135: defpt := 1;
3136: END IF;
3137: IF l_party_site_contains_str IS NULL THEN
3138: defps := 1;
3139: END IF;
3140: IF l_contact_contains_str IS NULL THEN
3141: defct := 1;

Line 3140: IF l_contact_contains_str IS NULL THEN

3136: END IF;
3137: IF l_party_site_contains_str IS NULL THEN
3138: defps := 1;
3139: END IF;
3140: IF l_contact_contains_str IS NULL THEN
3141: defct := 1;
3142: END IF;
3143: IF l_contact_pt_contains_str IS NULL THEN
3144: defcpt := 1;

Line 3143: IF l_contact_pt_contains_str IS NULL THEN

3139: END IF;
3140: IF l_contact_contains_str IS NULL THEN
3141: defct := 1;
3142: END IF;
3143: IF l_contact_pt_contains_str IS NULL THEN
3144: defcpt := 1;
3145: END IF;
3146:
3147: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3148: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3149: 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);
3150: END IF;
3151: 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);
3152: 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);
3153: 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);
3154: 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);
3155: 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);
3156: 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 3153: 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);

3149: 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);
3150: END IF;
3151: 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);
3152: 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);
3153: 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);
3154: 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);
3155: 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);
3156: 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);
3157: END IF;

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

3150: END IF;
3151: 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);
3152: 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);
3153: 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);
3154: 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);
3155: 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);
3156: 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);
3157: END IF;
3158: FOR I in 1..5 LOOP

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

3151: 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);
3152: 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);
3153: 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);
3154: 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);
3155: 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);
3156: 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);
3157: END IF;
3158: FOR I in 1..5 LOOP
3159: do_eval(I);

Line 3269: l_party_contains_str VARCHAR2(32000);

3265: x_num_matches OUT NUMBER
3266: ) IS
3267:
3268: -- Strings to hold the generated Intermedia query strings
3269: l_party_contains_str VARCHAR2(32000);
3270: l_party_site_contains_str VARCHAR2(32000);
3271: l_contact_contains_str VARCHAR2(32000);
3272: l_contact_pt_contains_str VARCHAR2(32000);
3273: l_denorm_str VARCHAR2(32000);

Line 3270: l_party_site_contains_str VARCHAR2(32000);

3266: ) IS
3267:
3268: -- Strings to hold the generated Intermedia query strings
3269: l_party_contains_str VARCHAR2(32000);
3270: l_party_site_contains_str VARCHAR2(32000);
3271: l_contact_contains_str VARCHAR2(32000);
3272: l_contact_pt_contains_str VARCHAR2(32000);
3273: l_denorm_str VARCHAR2(32000);
3274: l_ps_denorm_str VARCHAR2(32000);

Line 3271: l_contact_contains_str VARCHAR2(32000);

3267:
3268: -- Strings to hold the generated Intermedia query strings
3269: l_party_contains_str VARCHAR2(32000);
3270: l_party_site_contains_str VARCHAR2(32000);
3271: l_contact_contains_str VARCHAR2(32000);
3272: l_contact_pt_contains_str VARCHAR2(32000);
3273: l_denorm_str VARCHAR2(32000);
3274: l_ps_denorm_str VARCHAR2(32000);
3275: l_ct_denorm_str VARCHAR2(32000);

Line 3272: l_contact_pt_contains_str VARCHAR2(32000);

3268: -- Strings to hold the generated Intermedia query strings
3269: l_party_contains_str VARCHAR2(32000);
3270: l_party_site_contains_str VARCHAR2(32000);
3271: l_contact_contains_str VARCHAR2(32000);
3272: l_contact_pt_contains_str VARCHAR2(32000);
3273: l_denorm_str VARCHAR2(32000);
3274: l_ps_denorm_str VARCHAR2(32000);
3275: l_ct_denorm_str VARCHAR2(32000);
3276: l_cpt_denorm_str VARCHAR2(32000);

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

3394: call_type(2) := 'OR';
3395: END;
3396:
3397: /** Private procedure to acquire and score at party level ***/
3398: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3399: l_party_id_idx NUMBER:=1;
3400: l_ctx_id NUMBER;
3401: BEGIN
3402: SAVEPOINT eval_start;

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

3415: l_search_ctx_id,H_PARTY_ID(I));
3416: l_party_id_idx := H_PARTY_ID.COUNT+1;
3417: ELSE
3418: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3419: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3420: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3421: 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);
3422: END IF;
3423: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3427: IF (l_party_site_contains_str IS NULL AND

3423: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3424: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3425: 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);
3426: END IF;
3427: IF (l_party_site_contains_str IS NULL AND
3428: l_contact_contains_str IS NULL AND
3429: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3430: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3431: RAISE L_RETURN_IMM_EXC;

Line 3428: l_contact_contains_str IS NULL AND

3424: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3425: 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);
3426: END IF;
3427: IF (l_party_site_contains_str IS NULL AND
3428: l_contact_contains_str IS NULL AND
3429: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3430: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3431: RAISE L_RETURN_IMM_EXC;
3432: ELSE

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

3425: 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);
3426: END IF;
3427: IF (l_party_site_contains_str IS NULL AND
3428: l_contact_contains_str IS NULL AND
3429: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3430: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3431: RAISE L_RETURN_IMM_EXC;
3432: ELSE
3433: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3429: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3430: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3431: RAISE L_RETURN_IMM_EXC;
3432: ELSE
3433: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3434: END IF;
3435: ELSE
3436: IF p_index>1 THEN
3437: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3449: l_ctx_id := NULL;
3450: l_party_id_idx := H_PARTY_ID.COUNT+1;
3451: END IF;
3452: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3453: 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);
3454: END IF;
3455: LOOP
3456: FETCH l_party_cur INTO
3457: l_party_id , l_TX2, l_TX8, l_TX4, l_TX41, l_TX44, l_TX46;

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

3471: l_party_id_idx:= l_party_id_idx+1;
3472: END IF;
3473: IF (l_party_id_idx-1)>l_max_thresh THEN
3474: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3475: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3476: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3477: AND l_contact_pt_contains_str IS NULL THEN
3478: H_PARTY_ID.DELETE(l_party_id_idx-1);
3479: H_PARTY_ID_LIST.DELETE(l_index);

Line 3477: AND l_contact_pt_contains_str IS NULL THEN

3473: IF (l_party_id_idx-1)>l_max_thresh THEN
3474: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3475: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3476: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3477: AND l_contact_pt_contains_str IS NULL THEN
3478: H_PARTY_ID.DELETE(l_party_id_idx-1);
3479: H_PARTY_ID_LIST.DELETE(l_index);
3480: H_SCORES.DELETE(l_index);
3481: EXIT;

Line 3506: p_contains_str VARCHAR2,

3502: CLOSE l_party_cur;
3503: ROLLBACK to eval_start;
3504: END;
3505: PROCEDURE open_person_contact_cursor(
3506: p_contains_str VARCHAR2,
3507: p_search_ctx_id NUMBER,
3508: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3509: BEGIN
3510: OPEN x_cursor FOR

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

3509: BEGIN
3510: OPEN x_cursor FOR
3511: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX2, TX6, TX5
3512: FROM HZ_STAGED_CONTACTS stage
3513: WHERE contains( concat_col, p_contains_str)>0
3514: AND ORG_CONTACT_ID in (
3515: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3516: org_contact_id
3517: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3533: end if;
3534: END;
3535:
3536: /** Private procedure to acquire and score at party site level ***/
3537: 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
3538: l_party_id_idx NUMBER:=1;
3539: l_ctx_id NUMBER;
3540: h_ps_id HZ_PARTY_SEARCH.IDList;
3541: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3561: ELSE
3562: l_party_id_idx := H_PARTY_ID.COUNT+1;
3563: l_ctx_id := NULL;
3564: END IF;
3565: 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);
3566: LOOP
3567: FETCH l_party_site_cur INTO
3568: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
3569: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3632: END IF;
3633: END;
3634:
3635: /** Private procedure to acquire and score at party site level ***/
3636: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3637: l_party_id_idx NUMBER:=1;
3638: l_ctx_id NUMBER;
3639: h_ct_id HZ_PARTY_SEARCH.IDList;
3640: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3648: FORALL I in 1..H_PARTY_ID.COUNT
3649: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3650: l_search_ctx_id,H_PARTY_ID(I));
3651: l_party_id_idx := H_PARTY_ID.COUNT+1;
3652: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3653: LOOP
3654: FETCH l_contact_cur INTO
3655: l_org_contact_id, l_ct_party_id , l_TX2, l_TX6, l_TX5;
3656: EXIT WHEN l_contact_cur%NOTFOUND;

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

3690: END IF;
3691: END;
3692:
3693: /** Private procedure to acquire and score at contact point level ***/
3694: 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
3695: l_party_id_idx NUMBER:=1;
3696: l_ctx_id NUMBER;
3697: h_cpt_id HZ_PARTY_SEARCH.IDList;
3698: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3735: END IF;
3736: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3737: 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);
3738: END IF;
3739: 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);
3740: LOOP
3741: FETCH l_contact_pt_cur INTO
3742: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;
3743: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3847: IF l_party_contains_str IS NOT NULL THEN

3843: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3844: l_threshold := 100;
3845: other_acq_criteria_exists := TRUE ;
3846: --check if acquisition criteria exists for any other entity
3847: IF l_party_contains_str IS NOT NULL THEN
3848: acq_cnt := 1;
3849: END IF;
3850: IF l_party_site_contains_str IS NOT NULL THEN
3851: acq_cnt := acq_cnt+1;

Line 3850: IF l_party_site_contains_str IS NOT NULL THEN

3846: --check if acquisition criteria exists for any other entity
3847: IF l_party_contains_str IS NOT NULL THEN
3848: acq_cnt := 1;
3849: END IF;
3850: IF l_party_site_contains_str IS NOT NULL THEN
3851: acq_cnt := acq_cnt+1;
3852: END IF;
3853: IF l_contact_contains_str IS NOT NULL THEN
3854: acq_cnt := acq_cnt+1;

Line 3853: IF l_contact_contains_str IS NOT NULL THEN

3849: END IF;
3850: IF l_party_site_contains_str IS NOT NULL THEN
3851: acq_cnt := acq_cnt+1;
3852: END IF;
3853: IF l_contact_contains_str IS NOT NULL THEN
3854: acq_cnt := acq_cnt+1;
3855: END IF;
3856: IF l_contact_pt_contains_str IS NOT NULL THEN
3857: acq_cnt := acq_cnt+1;

Line 3856: IF l_contact_pt_contains_str IS NOT NULL THEN

3852: END IF;
3853: IF l_contact_contains_str IS NOT NULL THEN
3854: acq_cnt := acq_cnt+1;
3855: END IF;
3856: IF l_contact_pt_contains_str IS NOT NULL THEN
3857: acq_cnt := acq_cnt+1;
3858: END IF;
3859: IF acq_cnt>1 THEN
3860: other_acq_criteria_exists := TRUE;

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

3886: FND_MSG_PUB.ADD;
3887: RAISE FND_API.G_EXC_ERROR;
3888: END IF;
3889: END IF;
3890: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3891: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3892: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3893: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3894: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3887: RAISE FND_API.G_EXC_ERROR;
3888: END IF;
3889: END IF;
3890: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3891: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3892: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3893: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3894: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3895: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3888: END IF;
3889: END IF;
3890: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3891: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3892: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3893: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3894: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3895: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3896: END IF;

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

3889: END IF;
3890: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3891: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3892: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3893: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3894: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3895: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3896: END IF;
3897: END;

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

3890: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3891: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3892: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3893: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3894: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3895: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3896: END IF;
3897: END;
3898: /************ End of find_persons private procedures **********/

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

3891: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3892: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3893: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3894: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3895: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3896: END IF;
3897: END;
3898: /************ End of find_persons private procedures **********/
3899:

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

3926: g_score_until_thresh := false;
3927: END IF;
3928: -- ************************************************************
3929: -- STEP 2. Setup of intermedia query strings for Acquisition query
3930: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3931: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3932: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3933: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3934: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

3927: END IF;
3928: -- ************************************************************
3929: -- STEP 2. Setup of intermedia query strings for Acquisition query
3930: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3931: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3932: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3933: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3934: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3935:

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

3928: -- ************************************************************
3929: -- STEP 2. Setup of intermedia query strings for Acquisition query
3930: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3931: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3932: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3933: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3934: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3935:
3936: -- Setup Search Context ID

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

3929: -- STEP 2. Setup of intermedia query strings for Acquisition query
3930: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3931: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3932: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3933: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3934: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3935:
3936: -- Setup Search Context ID
3937: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3940: IF l_party_contains_str IS NULL THEN

3936: -- Setup Search Context ID
3937: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3938: x_search_ctx_id := l_search_ctx_id;
3939:
3940: IF l_party_contains_str IS NULL THEN
3941: defpt := 1;
3942: END IF;
3943: IF l_party_site_contains_str IS NULL THEN
3944: defps := 1;

Line 3943: IF l_party_site_contains_str IS NULL THEN

3939:
3940: IF l_party_contains_str IS NULL THEN
3941: defpt := 1;
3942: END IF;
3943: IF l_party_site_contains_str IS NULL THEN
3944: defps := 1;
3945: END IF;
3946: IF l_contact_contains_str IS NULL THEN
3947: defct := 1;

Line 3946: IF l_contact_contains_str IS NULL THEN

3942: END IF;
3943: IF l_party_site_contains_str IS NULL THEN
3944: defps := 1;
3945: END IF;
3946: IF l_contact_contains_str IS NULL THEN
3947: defct := 1;
3948: END IF;
3949: IF l_contact_pt_contains_str IS NULL THEN
3950: defcpt := 1;

Line 3949: IF l_contact_pt_contains_str IS NULL THEN

3945: END IF;
3946: IF l_contact_contains_str IS NULL THEN
3947: defct := 1;
3948: END IF;
3949: IF l_contact_pt_contains_str IS NULL THEN
3950: defcpt := 1;
3951: END IF;
3952:
3953: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3951: END IF;
3952:
3953: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3954: 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);
3955: 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);
3956: 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);
3957: 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);
3958: 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);
3959: 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 3956: 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);

3952:
3953: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3954: 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);
3955: 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);
3956: 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);
3957: 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);
3958: 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);
3959: 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);
3960: END IF;

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

3953: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3954: 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);
3955: 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);
3956: 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);
3957: 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);
3958: 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);
3959: 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);
3960: END IF;
3961: FOR I in 1..5 LOOP

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

3954: 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);
3955: 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);
3956: 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);
3957: 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);
3958: 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);
3959: 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);
3960: END IF;
3961: FOR I in 1..5 LOOP
3962: do_eval(I);

Line 3964: IF l_contact_contains_str IS NOT NULL THEN

3960: END IF;
3961: FOR I in 1..5 LOOP
3962: do_eval(I);
3963: END LOOP;
3964: IF l_contact_contains_str IS NOT NULL THEN
3965: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3966: END IF;
3967: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3968: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 22 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3961: FOR I in 1..5 LOOP
3962: do_eval(I);
3963: END LOOP;
3964: IF l_contact_contains_str IS NOT NULL THEN
3965: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3966: END IF;
3967: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3968: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 22 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3969: END IF;

Line 4790: l_party_contains_str VARCHAR2(32000);

4786: x_num_matches OUT NUMBER
4787: ) IS
4788:
4789: -- Strings to hold the generated Intermedia query strings
4790: l_party_contains_str VARCHAR2(32000);
4791: l_match_idx NUMBER;
4792: l_party_site_contains_str VARCHAR2(32000);
4793: l_contact_contains_str VARCHAR2(32000);
4794: l_contact_pt_contains_str VARCHAR2(32000);

Line 4792: l_party_site_contains_str VARCHAR2(32000);

4788:
4789: -- Strings to hold the generated Intermedia query strings
4790: l_party_contains_str VARCHAR2(32000);
4791: l_match_idx NUMBER;
4792: l_party_site_contains_str VARCHAR2(32000);
4793: l_contact_contains_str VARCHAR2(32000);
4794: l_contact_pt_contains_str VARCHAR2(32000);
4795: l_tmp VARCHAR2(32000);
4796:

Line 4793: l_contact_contains_str VARCHAR2(32000);

4789: -- Strings to hold the generated Intermedia query strings
4790: l_party_contains_str VARCHAR2(32000);
4791: l_match_idx NUMBER;
4792: l_party_site_contains_str VARCHAR2(32000);
4793: l_contact_contains_str VARCHAR2(32000);
4794: l_contact_pt_contains_str VARCHAR2(32000);
4795: l_tmp VARCHAR2(32000);
4796:
4797: -- Other local variables

Line 4794: l_contact_pt_contains_str VARCHAR2(32000);

4790: l_party_contains_str VARCHAR2(32000);
4791: l_match_idx NUMBER;
4792: l_party_site_contains_str VARCHAR2(32000);
4793: l_contact_contains_str VARCHAR2(32000);
4794: l_contact_pt_contains_str VARCHAR2(32000);
4795: l_tmp VARCHAR2(32000);
4796:
4797: -- Other local variables
4798: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4867: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4868:
4869: -- ************************************************************
4870: -- STEP 2. Setup of intermedia query strings for Acquisition query
4871: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4872: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4873: 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);
4874:
4875: -- Setup Search Context ID

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

4868:
4869: -- ************************************************************
4870: -- STEP 2. Setup of intermedia query strings for Acquisition query
4871: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4872: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4873: 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);
4874:
4875: -- Setup Search Context ID
4876: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4877: x_search_ctx_id := l_search_ctx_id;
4878:
4879: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4880: 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);
4881: 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);
4882: 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);
4883: 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);
4884: END IF;
4885: IF l_party_site_contains_str IS NULL THEN

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

4878:
4879: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4880: 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);
4881: 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);
4882: 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);
4883: 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);
4884: END IF;
4885: IF l_party_site_contains_str IS NULL THEN
4886: defps := 1;

Line 4885: IF l_party_site_contains_str IS NULL THEN

4881: 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);
4882: 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);
4883: 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);
4884: END IF;
4885: IF l_party_site_contains_str IS NULL THEN
4886: defps := 1;
4887: END IF;
4888: IF l_contact_pt_contains_str IS NULL THEN
4889: defcpt := 1;

Line 4888: IF l_contact_pt_contains_str IS NULL THEN

4884: END IF;
4885: IF l_party_site_contains_str IS NULL THEN
4886: defps := 1;
4887: END IF;
4888: IF l_contact_pt_contains_str IS NULL THEN
4889: defcpt := 1;
4890: END IF;
4891:
4892: IF l_party_site_contains_str IS NOT NULL THEN

Line 4892: IF l_party_site_contains_str IS NOT NULL THEN

4888: IF l_contact_pt_contains_str IS NULL THEN
4889: defcpt := 1;
4890: END IF;
4891:
4892: IF l_party_site_contains_str IS NOT NULL THEN
4893: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4894: LOOP
4895: FETCH l_party_site_cur INTO
4896: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;

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

4889: defcpt := 1;
4890: END IF;
4891:
4892: IF l_party_site_contains_str IS NOT NULL THEN
4893: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4894: LOOP
4895: FETCH l_party_site_cur INTO
4896: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
4897: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4916: IF l_contact_pt_contains_str IS NOT NULL THEN

4912: END LOOP;
4913: CLOSE l_party_site_cur;
4914: END IF;
4915:
4916: IF l_contact_pt_contains_str IS NOT NULL THEN
4917: SAVEPOINT eval_start;
4918: IF l_match_str = ' AND ' OR (
4919: ((l_cpt_max_score/l_entered_max_score)<(100/445))
4920: ) THEN

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

4920: ) THEN
4921: FORALL I in 1..l_match_ps_list.COUNT
4922: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4923: values (l_search_ctx_id,l_match_ps_list(I));
4924: 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');
4925: ELSE
4926: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4927: END IF;
4928: LOOP

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

4922: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4923: values (l_search_ctx_id,l_match_ps_list(I));
4924: 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');
4925: ELSE
4926: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4927: END IF;
4928: LOOP
4929: FETCH l_contact_pt_cur INTO
4930: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;

Line 5026: l_party_contains_str VARCHAR2(32000);

5022: x_num_matches OUT NUMBER
5023: ) IS
5024:
5025: -- Strings to hold the generated Intermedia query strings
5026: l_party_contains_str VARCHAR2(32000);
5027: l_party_site_contains_str VARCHAR2(32000);
5028: l_contact_contains_str VARCHAR2(32000);
5029: l_contact_pt_contains_str VARCHAR2(32000);
5030: l_tmp VARCHAR2(32000);

Line 5027: l_party_site_contains_str VARCHAR2(32000);

5023: ) IS
5024:
5025: -- Strings to hold the generated Intermedia query strings
5026: l_party_contains_str VARCHAR2(32000);
5027: l_party_site_contains_str VARCHAR2(32000);
5028: l_contact_contains_str VARCHAR2(32000);
5029: l_contact_pt_contains_str VARCHAR2(32000);
5030: l_tmp VARCHAR2(32000);
5031:

Line 5028: l_contact_contains_str VARCHAR2(32000);

5024:
5025: -- Strings to hold the generated Intermedia query strings
5026: l_party_contains_str VARCHAR2(32000);
5027: l_party_site_contains_str VARCHAR2(32000);
5028: l_contact_contains_str VARCHAR2(32000);
5029: l_contact_pt_contains_str VARCHAR2(32000);
5030: l_tmp VARCHAR2(32000);
5031:
5032: -- Other local variables

Line 5029: l_contact_pt_contains_str VARCHAR2(32000);

5025: -- Strings to hold the generated Intermedia query strings
5026: l_party_contains_str VARCHAR2(32000);
5027: l_party_site_contains_str VARCHAR2(32000);
5028: l_contact_contains_str VARCHAR2(32000);
5029: l_contact_pt_contains_str VARCHAR2(32000);
5030: l_tmp VARCHAR2(32000);
5031:
5032: -- Other local variables
5033: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5100: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5101:
5102: -- ************************************************************
5103: -- STEP 2. Setup of intermedia query strings for Acquisition query
5104: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5105: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5106: 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);
5107:
5108: -- Setup Search Context ID

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

5101:
5102: -- ************************************************************
5103: -- STEP 2. Setup of intermedia query strings for Acquisition query
5104: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5105: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5106: 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);
5107:
5108: -- Setup Search Context ID
5109: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5110: x_search_ctx_id := l_search_ctx_id;
5111:
5112: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5113: 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);
5114: 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);
5115: 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);
5116: 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);
5117: END IF;
5118: IF l_contact_contains_str IS NULL THEN

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

5111:
5112: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5113: 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);
5114: 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);
5115: 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);
5116: 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);
5117: END IF;
5118: IF l_contact_contains_str IS NULL THEN
5119: defct := 1;

Line 5118: IF l_contact_contains_str IS NULL THEN

5114: 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);
5115: 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);
5116: 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);
5117: END IF;
5118: IF l_contact_contains_str IS NULL THEN
5119: defct := 1;
5120: END IF;
5121: IF l_contact_pt_contains_str IS NULL THEN
5122: defcpt := 1;

Line 5121: IF l_contact_pt_contains_str IS NULL THEN

5117: END IF;
5118: IF l_contact_contains_str IS NULL THEN
5119: defct := 1;
5120: END IF;
5121: IF l_contact_pt_contains_str IS NULL THEN
5122: defcpt := 1;
5123: END IF;
5124:
5125: IF l_contact_contains_str IS NOT NULL THEN

Line 5125: IF l_contact_contains_str IS NOT NULL THEN

5121: IF l_contact_pt_contains_str IS NULL THEN
5122: defcpt := 1;
5123: END IF;
5124:
5125: IF l_contact_contains_str IS NOT NULL THEN
5126: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5127: LOOP
5128: FETCH l_contact_cur INTO
5129: l_org_contact_id, l_ct_party_id , l_TX2, l_TX6, l_TX5;

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

5122: defcpt := 1;
5123: END IF;
5124:
5125: IF l_contact_contains_str IS NOT NULL THEN
5126: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5127: LOOP
5128: FETCH l_contact_cur INTO
5129: l_org_contact_id, l_ct_party_id , l_TX2, l_TX6, l_TX5;
5130: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5147: IF l_contact_pt_contains_str IS NOT NULL THEN

5143: END LOOP;
5144: CLOSE l_contact_cur;
5145: END IF;
5146:
5147: IF l_contact_pt_contains_str IS NOT NULL THEN
5148: SAVEPOINT eval_start;
5149: IF l_match_str = ' AND ' OR (
5150: ((l_cpt_max_score/l_entered_max_score)<(100/445))
5151: ) THEN

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

5151: ) THEN
5152: FORALL I in 1..l_match_contact_list.COUNT
5153: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5154: values (l_search_ctx_id,l_match_contact_list(I));
5155: 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');
5156: ELSE
5157: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5158: END IF;
5159: LOOP

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

5153: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5154: values (l_search_ctx_id,l_match_contact_list(I));
5155: 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');
5156: ELSE
5157: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5158: END IF;
5159: LOOP
5160: FETCH l_contact_pt_cur INTO
5161: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;

Line 5255: l_contact_pt_contains_str VARCHAR2(32000);

5251: ) IS
5252:
5253:
5254: -- Strings to hold the generated Intermedia query strings
5255: l_contact_pt_contains_str VARCHAR2(32000);
5256: l_tmp VARCHAR2(32000);
5257:
5258: -- Other local variables
5259: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5310: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5311:
5312: -- ************************************************************
5313: -- STEP 2. Setup of intermedia query strings for Acquisition query
5314: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5315: 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);
5316:
5317: -- Setup Search Context ID
5318: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5318: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5319: x_search_ctx_id := l_search_ctx_id;
5320: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5321: 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);
5322: 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);
5323: 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);
5324: END IF;
5325:
5326: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 5326: IF l_contact_pt_contains_str IS NOT NULL THEN

5322: 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);
5323: 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);
5324: END IF;
5325:
5326: IF l_contact_pt_contains_str IS NOT NULL THEN
5327: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5328: cnt := 1;
5329: LOOP
5330: FETCH l_contact_pt_cur INTO

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

5323: 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);
5324: END IF;
5325:
5326: IF l_contact_pt_contains_str IS NOT NULL THEN
5327: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5328: cnt := 1;
5329: LOOP
5330: FETCH l_contact_pt_cur INTO
5331: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;

Line 5398: l_party_contains_str VARCHAR2(32000);

5394: x_search_ctx_id IN OUT NUMBER
5395: ) IS
5396:
5397: -- Strings to hold the generated Intermedia query strings
5398: l_party_contains_str VARCHAR2(32000);
5399: l_party_site_contains_str VARCHAR2(32000);
5400: l_contact_contains_str VARCHAR2(32000);
5401: l_contact_pt_contains_str VARCHAR2(32000);
5402: l_tmp VARCHAR2(32000);

Line 5399: l_party_site_contains_str VARCHAR2(32000);

5395: ) IS
5396:
5397: -- Strings to hold the generated Intermedia query strings
5398: l_party_contains_str VARCHAR2(32000);
5399: l_party_site_contains_str VARCHAR2(32000);
5400: l_contact_contains_str VARCHAR2(32000);
5401: l_contact_pt_contains_str VARCHAR2(32000);
5402: l_tmp VARCHAR2(32000);
5403:

Line 5400: l_contact_contains_str VARCHAR2(32000);

5396:
5397: -- Strings to hold the generated Intermedia query strings
5398: l_party_contains_str VARCHAR2(32000);
5399: l_party_site_contains_str VARCHAR2(32000);
5400: l_contact_contains_str VARCHAR2(32000);
5401: l_contact_pt_contains_str VARCHAR2(32000);
5402: l_tmp VARCHAR2(32000);
5403:
5404: -- Other local variables

Line 5401: l_contact_pt_contains_str VARCHAR2(32000);

5397: -- Strings to hold the generated Intermedia query strings
5398: l_party_contains_str VARCHAR2(32000);
5399: l_party_site_contains_str VARCHAR2(32000);
5400: l_contact_contains_str VARCHAR2(32000);
5401: l_contact_pt_contains_str VARCHAR2(32000);
5402: l_tmp VARCHAR2(32000);
5403:
5404: -- Other local variables
5405: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5476: g_score_until_thresh := false;
5477: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5478: -- ************************************************************
5479: -- STEP 2. Setup of intermedia query strings for Acquisition query
5480: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5481: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5482: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5483: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5484:

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

5477: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5478: -- ************************************************************
5479: -- STEP 2. Setup of intermedia query strings for Acquisition query
5480: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5481: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5482: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5483: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5484:
5485: -- Setup Search Context ID

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

5478: -- ************************************************************
5479: -- STEP 2. Setup of intermedia query strings for Acquisition query
5480: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5481: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5482: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5483: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5484:
5485: -- Setup Search Context ID
5486: IF x_search_ctx_id IS NULL THEN

Line 5502: IF l_party_site_contains_str IS NOT NULL THEN

5498: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX2,l_TX8,l_TX4,l_TX41,l_TX44,l_TX46,1);
5499: END LOOP;
5500: CLOSE l_party_cur;
5501:
5502: IF l_party_site_contains_str IS NOT NULL THEN
5503: l_max_score := 0;
5504: l_max_id := 0;
5505: l_max_idx := 0;
5506: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5503: l_max_score := 0;
5504: l_max_id := 0;
5505: l_max_idx := 0;
5506: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5507: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5508: ELSE
5509: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5510: END IF;
5511: LOOP

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

5505: l_max_idx := 0;
5506: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5507: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5508: ELSE
5509: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5510: END IF;
5511: LOOP
5512: FETCH l_party_site_cur INTO
5513: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX11;

Line 5531: IF l_contact_contains_str IS NOT NULL THEN

5527: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX3,l_max_TX4,l_max_TX11,l_max_idx);
5528: END IF;
5529: END IF;
5530:
5531: IF l_contact_contains_str IS NOT NULL THEN
5532: l_max_score := 0;
5533: l_max_id := 0;
5534: l_max_idx := 0;
5535: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5531: IF l_contact_contains_str IS NOT NULL THEN
5532: l_max_score := 0;
5533: l_max_id := 0;
5534: l_max_idx := 0;
5535: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5536: LOOP
5537: FETCH l_contact_cur INTO
5538: l_org_contact_id, l_ct_party_id , l_TX2, l_TX6, l_TX5;
5539: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5556: IF l_contact_pt_contains_str IS NOT NULL THEN

5552: 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_TX2,l_max_TX6,l_max_TX5,l_max_idx);
5553: END IF;
5554: END IF;
5555:
5556: IF l_contact_pt_contains_str IS NOT NULL THEN
5557: l_max_score := 0;
5558: l_max_id := 0;
5559: l_max_idx := 0;
5560: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5557: l_max_score := 0;
5558: l_max_id := 0;
5559: l_max_idx := 0;
5560: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5561: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5562: ELSE
5563: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5564: END IF;
5565: LOOP

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

5559: l_max_idx := 0;
5560: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5561: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5562: ELSE
5563: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5564: END IF;
5565: LOOP
5566: FETCH l_contact_pt_cur INTO
5567: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;